Description
This covers how to use Qlik to insert data from a Qlik table into a SQL database using script generation. This is done for all tables within a Qlik model where the Qlik and Database table and field names are the same.
*There is no data type validation/manipulation aside from making blanks NULL
s.
Introduction
Before I get into the code, there are a few things that I want to cover.
INSERT
We will be inserting the data from Qlik into the database table using traditional SQL. If you are not familiar with SQL, it is quite simple. First you define the table and columns you are going to insert into and then the values you want to insert. data are mapped to the columns by listed order.
Syntax:
INSERT INTO table_name (column_name_1, column_name_2,... column_name_n)
VALUES (%column_name_1_value%, %column_name_2_value%,... %column_name_n_value%);
If you want to upload more than one row, then you just need to add additional VALUE
parameters within parenthesis and separated by a comma.
INSERT INTO table_name (column_name_1, column_name_2,... column_name_n)
VALUES (%column_name_1_value%, %column_name_2_value%,... %column_name_n_value%),
(%column_name_1_value%, %column_name_2_value%,... %column_name_n_value%),
(%column_name_1_value%, %column_name_2_value%,... %column_name_n_value%);
For more info take a look at this tutorial.
One big difference between Qlik and SQL is that data types are strictly enforced. If you try to insert a string value into a field that is an int (integer) field, you will get an error. This will be touched on later.
!EXECUTE_NON_SELECT_QUERY
When sending a query in Qlik to a database, Qlik expects a response. Ideally some data but sometimes and error. However, when we are writing data to the database, Qlik doesn’t receive the response it expects. To overcome this we have to do two things.
- Enable
allow-nonselect-queries
in fileC:\Program Files\Common Files\Qlik\Custom Data\QvOdbcConnectorPackage\QvOdbcConnectorPackage.exe.config
by setting the parameter toTrue
. - Append your query with
!EXECTUTE_NON_SELECT_QUERY
- Put this before the semi-colon of the query.
Qlik Example:
SQL
INSERT INTO QlikLog (ID, App_GUID, Comment)
VALUES
(1, 'e6b9511f-bc25-4e65-ab7c-25bf0f5c8a93', 'Testing SQL Insert')
(2, 'e6b9511f-bc25-4e65-ab7c-25bf0f5c8a93', 'Testing Multiple Rows')
!EXECUTE_NON_SELECT_QUERY;
Subroutine
So given we need to format the Qlik data into a SQL statement, we will need to do some prep work. This basically entails combining the data from multiple columns into a single column within some additional formatting.
I have made the following subroutine to do this:
ConvertToSQLValues
Type | Name | Data Type | Desc |
---|---|---|---|
Parameter | pTable | string | Internal Qlik Table Name. |
Parameter | pBatchSize | integer | Number of rows to include in a single statement. |
Return | [%pTable%.SQLValues] | Qlik Table | Table containing the following return fields. |
Return | [%pTable%.SQLValues] .[%pTable%.Insert] | string | Field containing SQL Insert header. |
Return | [%pTable%.SQLValues] .[%pTable%.Values] | string | Field containing SQL Values statement. |
Return | [%pTable%.SQLValues] .[%pTable%.BatchId] | integer | “Field containing a Batch ID, used to group the row values. |
Note:
This subroutine does not account for any data type validation. However, one data cleansing thing it does is replace blanks with
NULL
s. This is so that when there is an empty value in a numeric field, we don’t pass an empty''
literal and fail the validation check. So if you have a value that is supposed to be blank in Qlik and notNULL
, this will be changed toNULL
.
Code
Sub ConvertToSQLValues(pTable,pBatchSize)
For ctsv.f=0 to NoOfFields('$(pTable)')
[ctsv.Field_tmp]:
Load
FieldName($(ctsv.f),'$(pTable)') as [ctsv.FieldName]
AutoGenerate
(1);
Next ctsv.f;
[ctsv.ConcatScript]:
Load
'SQL INSERT INTO $(pTable) (' & Concat([ctsv.FieldName],','&chr(10)) & ')' as [ctsv.InsertClause],
'Chr(39) & ' & Concat([ctsv.FieldName],' & chr(39) & Chr(44) & chr(39) & ') & ' & Chr(39)' as [ctsv.ValueConcat]
Resident
ctsv.Field_tmp;
Let ctsv.vInsertClause = Peek('ctsv.InsertClause', 0, 'ctsv.ConcatScript');
Let ctsv.vValueConcat = Peek('ctsv.ValueConcat', 0, 'ctsv.ConcatScript');
Drop Tables [ctsv.Field_tmp],[ctsv.ConcatScript];
[$(pTable).SQLValues]:
Load
'$(ctsv.vInsertClause)' as [$(pTable).Insert],
[$(pTable).BatchId] as [$(pTable).BatchId],
Concat([$(pTable).Values],','&Chr(10)) as [$(pTable).Values]
Group by
[$(pTable).BatchId];
Load
Ceil((RecNo()/$(pBatchSize))) as [$(pTable).BatchId],
Replace('('&$(ctsv.vValueConcat)&')', Chr(39)&Chr(39), 'NULL') as [$(pTable).Values]
Resident
[$(pTable)];
ctsv.f=;ctsv.vInsertClause=;ctsv.vValueConcat=;
End Sub;
Example
Input Table
Output Table
Code
Test:
Load
'ID-'&RowNo() as ID,
Round(Rand(),0.00) as Random,
'Two' as Two,
Null() as Null
AutoGenerate
(10);
Sub ConvertToSQLValues(pTable,pBatchSize)
For ctsv.f=0 to NoOfFields('$(pTable)')
[ctsv.Field_tmp]:
Load
FieldName($(ctsv.f),'$(pTable)') as [ctsv.FieldName]
AutoGenerate
(1);
Next ctsv.f;
[ctsv.ConcatScript]:
Load
'SQL INSERT INTO $(pTable) (' & Concat([ctsv.FieldName],',') & ')' as [ctsv.InsertClause],
'Chr(39) & ' & Concat([ctsv.FieldName],' & chr(39) & Chr(44) & chr(39) & ') & ' & Chr(39)' as [ctsv.ValueConcat]
Resident
ctsv.Field_tmp;
Let ctsv.vInsertClause = Peek('ctsv.InsertClause', 0, 'ctsv.ConcatScript');
Let ctsv.vValueConcat = Peek('ctsv.ValueConcat', 0, 'ctsv.ConcatScript');
Drop Tables [ctsv.Field_tmp],[ctsv.ConcatScript];
[$(pTable).SQLValues]:
Load
'$(ctsv.vInsertClause)' as [$(pTable).Insert],
[$(pTable).BatchId] as [$(pTable).BatchId],
Concat([$(pTable).Values],','&Chr(10)) as [$(pTable).Values]
Group by
[$(pTable).BatchId];
Load
Ceil((RecNo()/$(pBatchSize))) as [$(pTable).BatchId],
Replace('Values('&$(ctsv.vValueConcat)&')', Chr(39)&Chr(39), 'NULL') as [$(pTable).Values]
Resident
[$(pTable)];
ctsv.f=;ctsv.vInsertClause=;ctsv.vValueConcat=;
End Sub;
Call ConvertToSQLValues('Test',3);
SQL
Once we have a method to format the data as we need it, all that’s left is for us to execute the SQL in the Qlik load script.
This can be done using a loop that does the following:
- Get Table Name
- CovertToSQLValues
- Generate Qlik SQL statement and store in variable
- Execute Qlik SQL statement variable using dollar sign expansion
- Repeat
Code
Let vDBConnection = '%Database Connection Name%';
Let vLB = chr(10); // Line Break
For t = 0 to NoOfTables() - 1
Let vTable = TableName($(t));
Call ConvertToSQLValues('$(vTable)',500);
For i = 0 to NoOfRows('$(vTable).SQLValues')-1
Let vSQL = 'Lib Connect To '& chr(39) & '$(vDBConnection)' &chr(39) &'; $(vLB)$(vLB)' &
Peek('$(vTable).Insert',$(i),'$(vTable).SQLValues') & '$(vLB)' &
'Values $(vLB)' & Peek('$(vTable).Values',$(i),'$(vTable).SQLValues') &
'$(vLB)$(vLB)!EXECUTE_NON_SELECT_QUERY;$(vLB)$(vLB)Disconnect;$(vLB)$(vLB)';
$(vSQL)
Drop Tables [$(vTable).SQLValues];
vSQL=;
Next i;
i=;
Next t;
t=;
Code Generation
The only thing worth mentioning in the loop is the code generation piece. What we are doing is creating a script statement using data in the return table created by ConvertToSQLValues
.
So vSQL
ends up being something like this:
Lib Connect To 'QVD Catalog DB';
SQL
INSERT INTO QlikLog (ID, App_GUID, Comment)
VALUES
(1, 'e6b9511f-bc25-4e65-ab7c-25bf0f5c8a93', 'Testing SQL Insert')
(2, 'e6b9511f-bc25-4e65-ab7c-25bf0f5c8a93', 'Testing Multiple Rows')
!EXECUTE_NON_SELECT_QUERY;
DisConnect;
*Note:
We are connecting and disconnecting on each SQL call. This isn’t mandatory, but done because I have seen issues when executing many calls against a single DB connection opening. This may take longer, but should alleviate weird occurrences.