Azure SQL Query

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.

Warning: This component is destructive as it truncates or recreates its target table on each run. Do not modify the target table structure manually.


Properties

Property Setting Description
Name Text The descriptive name for the component.
Azure SQL Server Name Choice The SQL Server to connect to. This list should be autopopulated by Matillion, providing that the instance credentials include access to said servers.
Database Name Text This is the name of the database on the selected Azure SQL Server.
Username Text This is your Azure SQL Server connection username.
Password Text This is your Azure SQL Server connection password. The password is masked so it can be set, but not read.
SQL Query Text 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 as sensible defaults are assumed.
Value A value for the given Parameter.
Please contact support if you think you require an advanced JDBC option.
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 Selection Clean Staged Files: Automatically remove UUID-based objects on the Blob Storage Container (if ON). Default is ON. Effectively decides whether to keep the staged data in the Azure Blob Storage Container or not.
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.
Recreate Target Table: Choose whether the component recreates its target table before the data load. If OFF, the existing table will be used.
File Prefix: A common prefix to give to the staging files.

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 source database and upload it to Azure Blob Storage.
Time Taken To Load The amount of time (in seconds) taken to execute a COPY statement to load the data into the target table from Azure Blob 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.