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.
|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.
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 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.|
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.