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.
|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.
This component makes the following values available to export into variables:
|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.|
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.