Exporting Data from Qlik to Database

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

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 file C:\Program Files\Common Files\Qlik\Custom Data\QvOdbcConnectorPackage\QvOdbcConnectorPackage.exe.config by setting the parameter to True.
Append your query with !EXECTUTE_NON_SELECT_QUERY
- Put this before the semi-colon of the query.

Documentation

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 NULLs. 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 not NULL, this will be changed to NULL.

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.

Description

Introduction

`INSERT`

`!EXECUTE_NON_SELECT_QUERY`

Qlik Example:

Subroutine

ConvertToSQLValues

Code

Example

Code

SQL

Code Generation

About Trey Smith

Leave a Reply Cancel reply