SQL Scripting for ETL - Revision history

Ollvihe: /* Calling Expression Script from SQL Script */

2023-08-31T13:39:20Z

Calling Expression Script from SQL Script

← Older revision		Revision as of 13:39, 31 August 2023
Line 70:		Line 70:

	In the above example, if the expression script returns a DataFrame, it is stored to the ''#ExampleTable'' temporary table in the scripting sandbox database.		In the above example, if the expression script returns a DataFrame, it is stored to the ''#ExampleTable'' temporary table in the scripting sandbox database.

			== Calling SQL Script from SQL Script ==
			In an SQL script, it's possible to call other SQL script using the [[SQL_Scripting_Commands#--.23Run\|--#Run]] command. For more information, see its [[SQL_Scripting_Commands#--.23Run\|documentation]] and [[Run_Script_Examples\|examples]].

	== Exception Handling ==		== Exception Handling ==

Ollvihe: /* Exception Handling */

2023-08-31T13:34:37Z

Exception Handling

@@ Line 54: / Line 54: @@
 (SELECT 'SheetName', 'Info')
 --#ShowReport</pre>
 == Exception Handling ==

Ollvihe: /* Introduction to ETL Scripting */

2023-08-31T13:04:22Z

Introduction to ETL Scripting

← Older revision		Revision as of 13:04, 31 August 2023
Line 3:		Line 3:
	__TOC__		__TOC__

	== Introduction to ~~ETL~~ Scripting ==		== Introduction to SQL Scripting ==

	The QPR ProcessAnalyzer ETL system enables data extraction, transformation and loading based on SQL queries implemented by scripts. Scripts are written in SQL and consist of standard SQL commands, QPR ProcessAnalyzer commands and special parameters related to QPR ProcessAnalyzer. Scripts are useful for performing several database related operations. More specifically, with the QPR ProcessAnalyzer ETL system it is possible to, for example:		The QPR ProcessAnalyzer ETL system enables data extraction, transformation and loading based on SQL queries implemented by scripts. Scripts are written in SQL and consist of standard SQL commands, QPR ProcessAnalyzer commands and special parameters related to QPR ProcessAnalyzer. Scripts are useful for performing several database related operations. More specifically, with the QPR ProcessAnalyzer ETL system it is possible to, for example:

Ollvihe: /* Notes for SQL Scripts */

2023-08-31T13:03:52Z

Notes for SQL Scripts

← Older revision		Revision as of 13:03, 31 August 2023
Line 95:		Line 95:
	When writing scripts with the QPR ProcessAnalyzer ETL system, the lines in the script that start with "--#" (without the quotes and without any preceding blank spaces at the beginning of the line) are treated as QPR ProcessAnalyzer Commands and SQL ignores these lines as comments. The supported QPR ProcessAnalyzer commands and some examples of how to use them in scripts are listed in [[SQL_Scripting_Commands\|here]].		When writing scripts with the QPR ProcessAnalyzer ETL system, the lines in the script that start with "--#" (without the quotes and without any preceding blank spaces at the beginning of the line) are treated as QPR ProcessAnalyzer Commands and SQL ignores these lines as comments. The supported QPR ProcessAnalyzer commands and some examples of how to use them in scripts are listed in [[SQL_Scripting_Commands\|here]].

	== ~~Notes~~ for SQL Scripts ==		== Tips for Writing SQL Scripts ==
	* It is important to follow the T-SQL syntax when writing SQL queries.		* It is important to follow the T-SQL syntax when writing SQL queries.
	* SQL is not case sensitive, so you can write either ''SELECT'' or ''select''. However, the parameter values and the QPR ProcessAnalyzer commands given as comments are case sensitive.		* SQL is not case sensitive, so you can write either ''SELECT'' or ''select''. However, the parameter values and the QPR ProcessAnalyzer commands given as comments are case sensitive.

Ollvihe: /* See Also */

2021-12-11T19:27:58Z

Ollvihe: /* See Also */

2021-12-11T19:27:51Z

Ollvihe at 22:21, 10 November 2021

2021-11-10T22:21:39Z

@@ Line 10: / Line 10: @@
 * read data from existing models and create reports
 * import data from existing models and create new models extending the current features of QPR ProcessAnalyzer
 == Script Variables ==
@@ Line 99: / Line 63: @@
 INTO #PACommandExceptions
 </pre>
 == Notes for SQL Scripts ==

Ollvihe: /* Notes for SQL Scripts */

2021-11-10T22:19:19Z

Notes for SQL Scripts

← Older revision		Revision as of 22:19, 10 November 2021
Line 100:		Line 100:
	</pre>		</pre>

	=== Notes for SQL Scripts ===		== Notes for SQL Scripts ==

	* It is important to follow the T-SQL syntax when writing SQL queries.		* It is important to follow the T-SQL syntax when writing SQL queries.
	* SQL is not case sensitive, so you can write either ''SELECT'' or ''select''. However, the parameter values and the QPR ProcessAnalyzer commands given as comments are case sensitive.		* SQL is not case sensitive, so you can write either ''SELECT'' or ''select''. However, the parameter values and the QPR ProcessAnalyzer commands given as comments are case sensitive.

Ollvihe: /* Walkthough to SQL Scripting */

2021-11-10T22:18:46Z

Walkthough to SQL Scripting

← Older revision		Revision as of 22:18, 10 November 2021
Line 29:		Line 29:
	SELECT is one of the most basic SQL commands you can use. The SELECT statement is used to extract data from a table. You can define the criteria for what data is selected for extraction. On line 1 of the example above, the SQL statement selects all the columns from the #Customers table. In addition, this produces the first input argument for the QPR ProcessAnalyzer command —#ShowReport by defining what to print.		SELECT is one of the most basic SQL commands you can use. The SELECT statement is used to extract data from a table. You can define the criteria for what data is selected for extraction. On line 1 of the example above, the SQL statement selects all the columns from the #Customers table. In addition, this produces the first input argument for the QPR ProcessAnalyzer command —#ShowReport by defining what to print.

	~~'''Note on SQL syntax:''' You may wonder why a temporary table #Customers is used instead of an ordinary table (~~The preceding hash symbol (#) indicates that ~~it is~~ a temporary table~~.) In the QPR ProcessAnalyzer Pro ETL system only temporary tables can be used~~. They exist only during the execution of the script so they are not stored permanently.		The preceding hash symbol (#) indicates that results are stored to a temporary table. They exist only during the execution of the script so they are not stored permanently.

	:Lines 2-3:		:Lines 2-3:

Ollvihe: /* Principles of QPR ProcessAnalyzer ETL Scripts */

2021-10-31T15:07:57Z

Principles of QPR ProcessAnalyzer ETL Scripts

← Older revision		Revision as of 15:07, 31 October 2021
Line 11:		Line 11:
	* import data from existing models and create new models extending the current features of QPR ProcessAnalyzer		* import data from existing models and create new models extending the current features of QPR ProcessAnalyzer

	== ~~Principles of QPR ProcessAnalyzer ETL Scripts~~ ==		== Walkthough to SQL Scripting ==

	Here is an example of a simple QPR ProcessAnalyzer ETL script that prints data taken from the SQL table #Customers:		Here is an example of a simple QPR ProcessAnalyzer ETL script that prints data taken from the SQL table #Customers:

← Older revision		Revision as of 19:27, 11 December 2021
Line 106:		Line 106:
	* When doing more advanced operations with scripts, you may run into the error messages such as: "The data types sql_variant and varchar are incompatible in the add operation.", "Argument data type sql_variant is invalid for argument 1 of like function.", "Argument data type sql_variant is invalid for argument 1 of left function.". This is because case attributes, event attributes, and data inside datatables are ''sql_variant'' type data. In order to use them with more advanced operations (e.g. Add), you need to CONVERT or CAST them into some other data type before the operations. See [[#ConvertExample\|this example]].		* When doing more advanced operations with scripts, you may run into the error messages such as: "The data types sql_variant and varchar are incompatible in the add operation.", "Argument data type sql_variant is invalid for argument 1 of like function.", "Argument data type sql_variant is invalid for argument 1 of left function.". This is because case attributes, event attributes, and data inside datatables are ''sql_variant'' type data. In order to use them with more advanced operations (e.g. Add), you need to CONVERT or CAST them into some other data type before the operations. See [[#ConvertExample\|this example]].
	* For certain characters in attribute values, you need to use escaping in order to have them interpreted correctly in the script.		* For certain characters in attribute values, you need to use escaping in order to have them interpreted correctly in the script.

	~~== See Also ==~~
	* [[Troubleshooting Scripts]]
	* For a list of supported commands and their descriptions, see page [[SQL Scripting Commands]]
	* For more examples, see [[ETL Script Examples]]

← Older revision		Revision as of 19:27, 11 December 2021
Line 108:		Line 108:

	== See Also ==		== See Also ==
	* [[Troubleshooting ~~QPR ProcessAnalyzer~~ Scripts]]		* [[Troubleshooting Scripts]]
	* For a list of supported commands and their descriptions, see page [[SQL Scripting Commands]]		* For a list of supported commands and their descriptions, see page [[SQL Scripting Commands]]
	* For more examples, see [[ETL Script Examples]]		* For more examples, see [[ETL Script Examples]]