Azure SQL Query

Azure SQL Query


This article is specific to the following platforms - Snowflake.

Azure SQL Query

Run an SQL Query on an Azure SQL Database and copy the result to a table, via Azure Blob Storage.

This component is for data-staging - getting data into a table in order to perform further processing and transformations on it. The target table should be considered temporary, as it will either be truncated or recreated each time the components runs.



Snowflake Properties

Property Setting Description
Name String Input the descriptive name for the component.
Basic/Advanced Mode Select Basic: This mode will build an Azure SQL query using settings from the Data Source, Data Selection, and Data Source Filter parameters. In most cases, this mode will be sufficient.
Advanced: This mode will require users to write an SQL-like query, which is translated into one or more Azure SQL API calls.
Azure SQL Server Name Select The SQL Server to connect to. This list should be autopopulated by Matillion, providing that the instance credentials include access to the corresponding servers.
Database Name String This is the name of the database on the selected Azure SQL Server.
Username String This is your Azure SQL Server connection username.
Password String This is your Azure SQL Server connection password. The password is masked so it can be set, but not read.
SQL Query String This is an SQL query, written in the dialect of the SQL database. This should be a simple select query.
JDBC Options Parameter A Microsoft SQL Server parameter.
These are usually not required, since sensible defaults are assumed.
Value A value for the given Parameter.
Please contact support if you think you require an advanced JDBC option.
Data Schema Select Select the data schema.
Data Source Select Select a data source.
Data Selection Select Select one or more columns from the chosen data source to return from the query.
Data Source Filter Input Column Select an input column for your filter. The available input columns vary depending upon the data source.
Qualifier Is: Compares the column to the value using the comparator.
Not: Reverses the effect of the comparison, so "Equals" becomes "Not equals", "Less than" becomes "Greater than or equal to", etc.
Comparator Select the comparator. Note: Not all comparators will work with all possible data sources.
Choose one of "Equal to", "Greater than", "Less than", "Greater than or equal to", "Less than or equal to", or "Like".
"Equal to" can match exact strings and numeric values, while other comparators such as "Great than" and "Less than" will work only with numerics. The "Like" comparator allows the wildcard character % to be used at the start and end of a string value to match a column. The "Null" comparator matches only null values, ignoring whatever the value is set to.
Note: Not all data sources support all comparators, meaning that, often, only a subset of the above comparators will be available for selection.
Value Specify value to be compared.
Combine Filters Select Use the defined filters in combination with one another according to either And or Or.
Limit Integer Set a numeric value to limit the number of rows that can be loaded.
Warehouse Select Choose a Snowflake warehouse that will run the load.
Database Select Select the database that the newly-created table will belong to.
Schema Select Select the table schema. The special value, [Environment Default] will use the schema defined in the environment. For more information on using multiple schemas, see this article.
Table Text Provide a name for the new table to load data into.
Warning: This table will be recreated and will drop any existing table of the same name.
Storage Account Select (Azure Only) Select a Storage Account with your desired Blob Container to be used for staging the data.
Blob Container Select (Azure Only) Select a Blob Container to be used for staging the data.
Load Options Multiple Select Clean Staged Files: Destroy staged files after loading data. Default is On.
String Null is Null: Converts any strings equal to "null" into a null value. This is case sensitive and only works with entirely lower-case strings. Default is Off.
Recreate Target Table: Choose whether the component recreates its target table before the data load. If Off, the component will use an existing table or create one if it does not exist. Default is On.
File Prefix: Give staged file names a prefix of your choice. The default setting is an empty field.
Trim String Columns: Remove leading and trailing characters from a string column. Default is On
Use Grid Variable: Check this checkbox to use a grid variable. This box is unchecked by default.

Variable Exports

This component makes the following values available to export into variables:

Source Description
Time Taken To Stage The amount of time (in seconds) taken to fetch the data from the data source and upload it to storage.
Time Taken To Load The amount of time (in seconds) taken to execute the COPY statement to load the data into the target table from storage.
>

Strategy

Connect to the Azure SQL Database and issue the query. Stream the results into objects on Azure Blob Storage. Then create or truncate the target table and issue a COPY command to load the Azure Blob Storage objects into the table. Finally, clean up the temporary Azure Blob Storage objects.