Does Qlik Replicate support replication to Azure Synapse with Serverless sql pool (not dedicated)?

[TomaszRomanowski]

I know that  Qlik Replicate supports replication to Azure Synapse (I tested it with Dedicated sql pool).

But can I use Serverless sql?

There is nothing in docs about it.

I have checked that during starting replication task, problem is starting with command's syntax that are generated by Qlik Replicate.

For instance at the begging there is:

00011473: 2022-04-01T09:05:46:547556 [TARGET_APPLY    ]V:  Execute immediate: 'CREATE EXTERNAL DATA SOURCE [ATTREP_DS_G2_DWH_******] WITH (TYPE=Hadoop, LOCATION= 'abfss://qlik****@*****.dfs.core.windows.net', CREDENTIAL = [SCOPEDCRE] );'  (ar_odbc_stmt.c:4897)

00011473: 2022-04-01T09:05:46:569350 [TARGET_APPLY    ]T:  RetCode: SQL_ERROR  SqlState: 42000 NativeError: 102 Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near 'HADOOP'. Line: 1 Column: -1 [1022502]  (ar_odbc_stmt.c:4981)

Without option "TYPE=Hadoop" this command can be run successfully on Serverless sql.

Maybe there is  special internal option for change it?

I also understand that in case of Synapse Serverless sql I can't create physical tables but we can only create External tables that are linked to csv files in ADLS. 

In some business cases it would be fine.

So the solution would be just:

• Replicate data into ADLS (now this works fine)
• Automatic create external tables in Synapse Serverless sql that link to files in ADLS

[Steve_Nguyen · Accepted solution]

@TomaszRomanowski currently it is not supported.

you can submit feature request :

https://community.qlik.com/t5/Ideation/ct-p/qlik-product-insight

[TomaszRomanowski]

Thanks Steve for clear answer.

I have created "simple" query to generate DDL for such tables based on *.dfm files (info schema generated by Qlik Replicate). Maybe it will be useful for someone.

select
'create EXTERNAL TABLE dbo."' + a.targetTable
+ '"
('
+ STRING_AGG(a.ColumnName+ ' ' +
case
when a.ColumnType = 'BOOL' then 'BIT'
when a.ColumnType = 'BOOLEAN' then 'BIT'
when a.ColumnType = 'BYTES' then (
case when a.ColumnLength < 8001 then 'VARBINARY (' + cast (a.ColumnLength as varchar(8)) + ')'
else 'VARBINARY (8000)' end
)
when a.ColumnType = 'DATE' then 'DATE'
when a.ColumnType = 'TIME' then 'TIME'
when a.ColumnType = 'DATETIME' then 'DATETIME2 (' + cast (a.ColumnScale as varchar(8)) + ')'
when a.ColumnType = 'INT1' then 'SMALLINT'
when a.ColumnType = 'INT2' then 'SMALLINT'
when a.ColumnType = 'INT4' then 'INTEGER'
when a.ColumnType = 'INT8' then 'BIGINT'
when a.ColumnType = 'NUMERIC' then 'DECIMAL (' + cast (a.ColumnPrecision as varchar(8)) + ',' + cast (a.ColumnScale as varchar(8)) + ')'
when a.ColumnType = 'REAL4' then 'FLOAT(24)'
when a.ColumnType = 'REAL8' then 'FLOAT(53)'
when a.ColumnType = 'STRING' then (
case when a.ColumnLength < 8001 then 'VARCHAR (' + cast (a.ColumnLength as varchar(8)) + ')'
else 'VARCHAR (8000)' end
)
when a.ColumnType = 'UINT1' then 'TINYINT'
when a.ColumnType = 'UINT2' then 'SMALLINT'
when a.ColumnType = 'UINT4' then 'INTEGER'
when a.ColumnType = 'UINT8' then 'BIGINT'
when a.ColumnType = 'WSTRING' then (
case when a.ColumnLength < 4001 then 'NVARCHAR (' + cast (a.ColumnLength as varchar(8)) + ')'
else 'NVARCHAR (4000)' end
)
when a.ColumnType = 'BLOB' then 'VARBINARY (MAX)'
when a.ColumnType = 'NCLOB' then 'NVARCHAR (4000)'
when a.ColumnType = 'CLOB' then 'VARCHAR (8000)'
else 'NO_TYPE('+a.ColumnType+')' end
,',')
+')
' +
'WITH (
LOCATION = ''<folder_path_in_adls>/dbo.'+ a.targetTable +'/LOAD*.csv.gz'',
DATA_SOURCE = [<ADLS_URL>],
FILE_FORMAT = [SynapseDelimitedTextFormat]
)
GO
'
from
(
select
JSON_VALUE(doc, '$.dataInfo.targetTable') AS targetTable,
ColumnName, ColumnType, ColumnLength, ColumnPrecision, ColumnScale, ColumnPrimaryKeyPos
from openrowset(
bulk '<folder_path_in_adls>/*/*.dfm',
data_source = '<ADLS_URL>',
format = 'csv',
fieldterminator ='0x0b',
fieldquote = '0x0b',
ROWTERMINATOR = '0x0b'
) with (doc nvarchar(max)) as rows
cross apply openjson (doc,'$.dataInfo.columns')
with (
ColumnName varchar(32) '$.name',
ColumnType varchar(32) '$.type',
ColumnLength int '$.length',
ColumnPrecision int '$.precision',
ColumnScale int '$.scale',
ColumnPrimaryKeyPos int '$.primaryKeyPos'
)
)a
group by a.targetTable