Meta Integration® Model Bridge (MIMB)
"Metadata Integration" Solution

MIMB Bridge Documentation

MIMB Import Bridge from Snowflake Database SQL DML (DI/ETL) Script (SnowSQL)

Bridge Specifications

Vendor Snowflake
Tool Name Snowflake Database
Tool Version 1
Tool Web Site https://www.snowflake.com/
Supported Methodology [Data Integration] Multi-Model, Data Store (Physical Data Model), (Source and Target Data Stores, Transformation Lineage, Expression Parsing) via SQL TXT File

SPECIFICATIONS
Tool: Snowflake Snowflake Database version 1 via SQL TXT File
See https://www.snowflake.com/
Metadata: [Data Integration] Multi-Model, Data Store (Physical Data Model), (Source and Target Data Stores, Transformation Lineage, Expression Parsing)
Bridge: SqlScriptSnowflake version 11.0.0

OVERVIEW
WARNING: This data warehouse SQL script import bridge should only be used for datastore external SQL scripts scheduled on regular basis typically for loading the database. Do not use this bridge for all the DDL SQL scripts used to create (or update) the database schemas, packages, tables, views, stored procedures, etc. (as they heavily depend on each other). Instead, use the dedicated live database import via JDBC which will generate a complete and detailed data flow lineage integrating all transformations with stored procedures, views, etc. (which might have been created by many such DDL SQL scripts).
The purpose of this Snowflake Data Warehouse SQL script import bridge is to detect and parse all its embedded SQL statements in order to generate the exact scope (data models) of the involved source and target data stores, as well as the data flow lineage and impact analysis (data integration ETL/ELT model) between them.


Bridge Parameters

Parameter Name Description Type Values Default Scope
Directory Select a directory with the textual files that contain scripts to import DIRECTORY     Mandatory
Include filter The include folder and file filter pattern relative to the root directory.
The patern uses extended unix glob case-sensitive expression syntax.
Here are some common examples:
*.* - include any file at the root level
*.csv - include only csv files at the root level
**.csv -include only csv files at any level
*.{csv,gz} include only csv or gz files at the root level
dir\*.csv - include only csv files in the 'dir' folder
dir\**.csv - include only csv files under 'dir' folder at any level
dir\**.* - include any file under 'dir' folder at any level
f.csv - include only f.csv under root level
**\f.csv - include only f.csv at any level
**dir\** - include all files under any 'dir' folder at any level
**dir1\dir2\** - include all files under any 'dir2' folder under any 'dir1' folder at any level
STRING      
Exclude filter The exclude folder and file filter pattern relative to the root directory.
The patern uses the same syntax as the Include filter. See it for the systax details and examples.
Files that match the exclude filter are skipped.
When both include and exclude filters are empty all folders and files under the Root directory are included.
When the include filter is empty and the exclude one is not folders and files under the Root directory are included except ones matching the exclude filter.
STRING      
Miscellaneous Specify miscellaneous options identified with a -option followed by a value if required:

GENERAL OPTIONS
-m <Java Memory's maximum size>
1G by default on 64bits JRE or as set in conf/conf.properties, e.g.
-m 8G
-m 2500M

-j <Java Runtime Environment command line options>
This option must be the last one in the Miscellaneous parameter as all the text after -j is passed "as is" to the JRE, e.g.
-j -Dname=value -Xms1G

-jre <Java Runtime Environment full path name>
It can be an absolute path to javaw.exe on Windows or a link/script path on Linux, e.g.
-jre "c:\Program Files\Java\jre1.8.0_211\bin\javaw.exe"

-v <Environment variable value>
None by default, e.g.
-v var1=value1 -v var2="value2 with spaces"

-model.name <model name>
Override the model name, e.g.
-model.name "My Model Name"

-prescript <script name>
The script must be located in the bin directory, and have .bat or .sh extension.
The script path must not include any parent directory symbol (..).
The script should return exit code 0 to indicate success, or another value to indicate failure.
For example:
-prescript \"script.bat\"

-pppd
Create the connections and connection data sets in DI/ETL design models. This feature should only be used when intending to export to another DI/ETL tool.

-s <filepath>
Path to a file that resolves Shell parameters in either Windows (%param%) or in Linux (${param}, $1) format. This parameter can be used to define a path to the key/value pair yaml file. The path can be escaped with double quotes if it contains spaces or any special characters. The records from the file will be used to preprocess the scripts and replace the corresponding Shell parameters with the actual values. The key literals must not be decorated with the escape characters and the matching rules are case sensitive. Character colon ':' is used as a key/value pair delimiter and must be escaped with backward slash '\' if it is part of the parameter name. For example, for script 'INSERT INTO %SCHEMA1%.t1(c1) SELECT a from %SCHEMA2%.t2;' the file with the parameters can be organized in the following way. E.g.
# common shell parameter map
SCHEMA1: actual_schema1
SCHEMA2: actual_schema2
# individual script maps
"D:\\MIMB\\hive_001.hql":
${table_name}: actual_table
"D:\\MIMB\\hive_002.hql":
${year_var}: 1993

If the bridge doesn't find yaml file then it generates new one and fills it with pairs of keys/default values.
Yaml file contains "common shell parameter map" section and "individual script maps" section. The bridge takes common key/value pairs to substitute shell parameters by its values in all scripts. The section "individual script maps" contains pairs for individual scripts.

SQL SCRIPT OPTIONS
-d
Default schema. Allows to specify a schema name for the objects that don't have it defined explicitly.

-e
Encoding. This value will be used to load text from the specified script files. By default, UTF-8 will be used. E.g.
-e UTF-16
-e UTF-16BE
-e US-ASCII

-cs
Create separate connections for all database schemas
-cs c1, c2 - create separate connections for all database schemas of 'c1' and 'c2' connections
-cs app1=c.s1 - create 'app1' connection for the 's1' schema in the 'c' connection
STRING      
Incremental import Specifies whether to import only the changes made in the source or to re-import everything (as specified in other parameters).

True - import only the changes made in the source.
False - import everything (as specified in other parameters).

An internal cache is maintained for each metadata source, which contains previously imported models. If this is the first import or if the internal cache has been deleted or corrupted, the bridge will behave as if this parameter is set to 'False'.
BOOLEAN
False
True
True  

 

Bridge Mapping

Mapping information is not available

Last updated on Fri, 31 Jul 2020 18:20:03

Copyright © Meta Integration Technology, Inc. 1997-2020 All Rights Reserved.

Meta Integration® is a registered trademark of Meta Integration Technology, Inc.
All other trademarks, trade names, service marks, and logos referenced herein belong to their respective companies.