topic Re: Microsoft Azure Databricks Delta ConnectionTimeout in Qlik Replicate

Microsoft Azure Databricks Delta ConnectionTimeout

charlesrinaldini_colas — Tue, 21 Sep 2021 05:25:11 GMT

We keep getting disconnected errors due to timeout when trying to run the COPY INTO command to a delta table. Our cluster does not look to be the culprit as it has a server load distribution of about 40%. It appears that we should increase the ConnectionTimeout in our JDBC url, but this does not seem to be an option in the endpoint connection setup.

https://kb.databricks.com/bi/query-timeout-jdbc-connector.html

Error:
SQL_ERROR SqlState: HYT00 NativeError: 72 Message: [Simba][Hardy] (72) Query execution timeout expired.

Failed (retcode -1) to execute statement: 'COPY INTO `jde_dw`.`f1202` FROM(SELECT _c0 as `FLAID`, cast(_c1 as DECIMAL(38,10)) as `FLCTRY`, cast(_c2 as DECIMAL(38,10)) as `FLFY`, _c3 as `FLFQ`, _c4 as `FLLT`, _c5 as `FLSBL`, _c6 as `FLCO`, cast(_c7 as DECIMAL(38,10)) as `FLAPYC`, cast(_c8 as DECIMAL(38,10)) as `FLAN01`, cast(_c9 as DECIMAL(38,10)) as `FLAN02`, cast(_c10 as DECIMAL(38,10)) as `FLAN03`, cast(_c11 as DECIMAL(38,10)) as `FLAN04`, cast(_c12 as DECIMAL(38,10)) as `FLAN05`, cast(_c13 as DECIMAL(38,10)) as `FLAN06`, cast(_c14 as DECIMAL(38,10)) as `FLAN07`, cast(_c15 as DECIMAL(38,10)) as `FLAN08`, cast(_c16 as DECIMAL(38,10)) as `FLAN09`, cast(_c17 as DECIMAL(38,10)) as `FLAN10`, cast(_c18 as DECIMAL(38,10)) as `FLAN11`, cast(_c19 as DECIMAL(38,10)) as `FLAN12`, cast(_c20 as DECIMAL(38,10)) as `FLAN13`, cast(_c21 as DECIMAL(38,10)) as `FLAN14`, cast(_c22 as DECIMAL(38,10)) as `FLAPYN`, cast(_c23 as DECIMAL(38,10)) as `FLAWTD`, cast(_c24 as DECIMAL(38,10)) as `FLBORG`, cast(_c25 as DECIMAL(38,10)) as `FLPOU`, cast(_c26 as DECIMAL(38,10)) as `FLPC`, cast(_c27 as DECIMAL(38,10)) as `FLTKER`, cast(_c28 as DECIMAL(38,10)) as `FLBREQ`, cast(_c29 as DECIMAL(38,10)) as `FLBAPR`, _c30 as `FLMCU`, _c31 as `FLOBJ`, _c32 as `FLSUB`, cast(_c33 as DECIMAL(38,10)) as `FLNUMB`, cast(_c34 as DECIMAL(38,10)) as `FLADLM`, _c35 as `FLADM`, _c36 as `FLITAC`, cast(_c37 as DECIMAL(38,10)) as `FLADMP`, _c38 as `FLADSN`, _c39 as `FLDIR1`, cast(_c40 as INT) as `FLDSD`, _c41 as `FLUSER`, cast(_c42 as INT) as `FLLCT`, _c43 as `FLPID`, _c44 as `FLSBLT`, _c45 as `FLCRCD`, cast(_c46 as INT) as `FLUPMJ`, _c47 as `FLJOBN`, cast(_c48 as DECIMAL(38,10)) as `FLUPMT`, _c49 as `FLCHCD`, _c50 as `FLDPCF`, cast(_c51 as DECIMAL(38,10)) as `FLCBXR`, cast(_c52 as TIMESTAMP) as `syncDateTime`, _c53 as `syncOperation`, _c54 as `syncFlag` from 'abfss://prep@pdcolaserdwhnsg2heusdl.dfs.core.windows.net') FILEFORMAT = CSV FILES = ('/jdetemp/JDE_DW_DL_PD_Large/5/LOAD00000032.csv.gz') FORMAT_OPTIONS('nullValue' = 'attrep_null', 'multiLine'='true') COPY_OPTIONS('force' = 'true')'

Re: Microsoft Azure Databricks Delta ConnectionTimeout

john_wang — Sun, 26 Sep 2021 06:39:24 GMT

Hello @charlesrinaldini_colas ,

As I explained in another comment , please try to adding internal parameters "executeTimeout" and/or "loadTimeout" in target endpoint properties, set one or both of them to proper higher values and run task again.

Hope this helps,

John.

Re: Microsoft Azure Databricks Delta ConnectionTimeout

charlesrinaldini_colas — Mon, 27 Sep 2021 01:12:37 GMT

Hi @john_wang ,

From reading other issues that were similar, we already tried to set both of these parameters, but even their default values seem to be ignored. This did not fix the problem.

Re: Microsoft Azure Databricks Delta ConnectionTimeout

john_wang — Mon, 27 Sep 2021 08:27:06 GMT

Hello @charlesrinaldini_colas ,

The timeout occurs while Replicate merge the changes from interim net change table to final target table. We may use the timeout control parameters or limit the target endpoint file attributes "Maximum file size" to reduce the CSV file size. However we need further information prior to a solution. I'd like suggest to open a support case with:

1- set target_apply to Verbose and reproduce the issue then upload the Diag Package to the case;

2- let's us know the source table creation DDL.

Please be in mind that set the logging level to Verbose may get big task log files generated. please set it back asap.

Regards,

John.

Re: Microsoft Azure Databricks Delta ConnectionTimeout

charlesrinaldini_colas — Tue, 28 Sep 2021 15:10:14 GMT

I've attached the diagnostic package and here is the create table command:

CREATE  TABLE `JDE_DW`.`F0618` (  `YTAN8` DECIMAL(38,10) NOT NULL, `YTPALF` STRING, `YTPRTR` DECIMAL(38,10) NOT NULL, `YTRCCD` STRING, `YTCKCN` DECIMAL(38,10), `YTAM` STRING, `YTCO` STRING, `YTHMCO` STRING, `YTHMCU` STRING, `YTMCU` STRING, `YTOBJ` STRING, `YTSUB` STRING, `YTRCO` STRING, `YTGMCU` STRING, `YTGOBJ` STRING, `YTGSUB` STRING, `YTSBL` STRING, `YTSBLT` STRING, `YTWR01` STRING, `YTMCUO` STRING, `YTMAIL` STRING, `YTPHRW` DECIMAL(38,10), `YTOPSQ` DECIMAL(38,10), `YTRILT` STRING, `YTITM` DECIMAL(38,10), `YTPCUN` DECIMAL(38,10), `YTUM` STRING, `YTPHRT` DECIMAL(38,10), `YTPPRT` DECIMAL(38,10), `YTBHRT` DECIMAL(38,10), `YTPBRT` DECIMAL(38,10), `YTBDRT` DECIMAL(38,10), `YTSHRT` DECIMAL(38,10), `YTSHFT` STRING, `YTSHD` DECIMAL(38,10), `YTPAYM` DECIMAL(38,10), `YTLD` STRING, `YTGPA` DECIMAL(38,10), `YTDPA` DECIMAL(38,10), `YTRCPY` DECIMAL(38,10), `YTSAMT` DECIMAL(38,10), `YTUN` STRING, `YTJBCD` STRING, `YTJBST` STRING, `YTWST` DECIMAL(38,10), `YTWCNT` DECIMAL(38,10), `YTWCTY` DECIMAL(38,10), `YTWCMP` STRING, `YTWET` STRING, `YTGENA` DECIMAL(38,10), `YTWCAM` DECIMAL(38,10), `YTWCMB` DECIMAL(38,10), `YTGENB` DECIMAL(38,10), `YTWCMO` DECIMAL(38,10), `YTGENO` DECIMAL(38,10), `YTWCMX` DECIMAL(38,10), `YTGENX` DECIMAL(38,10), `YTWCBN` DECIMAL(38,10), `YTHMO` DECIMAL(38,10), `YTPDBA` DECIMAL(38,10), `YTPB` STRING, `YTDEDM` STRING, `YTSALY` STRING, `YTNMTH` STRING, `YTPFRQ` STRING, `YTFY` DECIMAL(38,10), `YTDGL` INT, `YTPN` DECIMAL(38,10), `YTDWK` INT NOT NULL, `YTDW` STRING, `YTPPED` INT, `YTPPP` STRING, `YTDTBT` DECIMAL(38,10), `YTTCDE` DECIMAL(38,10), `YTEQCO` STRING, `YTEQWO` STRING, `YTEQCG` STRING, `YTQOBJ` STRING, `YTERC` STRING, `YTEQRT` DECIMAL(38,10), `YTEQGR` DECIMAL(38,10), `YTEQHR` DECIMAL(38,10), `YTEXR` STRING, `YTP001` STRING, `YTP002` STRING, `YTP003` STRING, `YTP004` STRING, `YTUSER` STRING, `YTCMMT` STRING, `YTCKDT` INT, `YTUAMT` DECIMAL(38,10), `YTRTWC` DECIMAL(38,10), `YTGENR` DECIMAL(38,10), `YTCKCS` STRING, `YTGICU` DECIMAL(38,10), `YTAID` STRING, `YTCMTH` STRING, `YTALT0` STRING, `YTPOS` STRING, `YTACTB` STRING, `YTABR1` STRING, `YTABT1` STRING, `YTABR2` STRING, `YTABT2` STRING, `YTABR3` STRING, `YTABT3` STRING, `YTABR4` STRING, `YTABT4` STRING, `YTBLGRT` DECIMAL(38,10), `YTRCHGAMT` DECIMAL(38,10), `YTFBLGRT` DECIMAL(38,10), `YTFRCHGAMT` DECIMAL(38,10), `YTCRR` DECIMAL(38,10), `YTCRCD` STRING, `YTCRDC` STRING, `YTRCHGMODE` STRING, `YTLTTP` STRING, `YTRKID` DECIMAL(38,10), `YTAPPRCFLG` STRING, `YTOTRULECD` STRING, `YTTSKID` DECIMAL(38,10), `YTTAXX` STRING, `YTSCTR` STRING, `YTSCRX` STRING, `YTSVH` STRING, `YTTCFD` INT, `YTTCTD` INT, `YTHWPD` DECIMAL(38,10), `YTINSTID` STRING, `YTJBLC` STRING, `YTCOPX` STRING, `YTBPTX` DECIMAL(38,10), `YTCOPB` DECIMAL(38,10), `YTCMED` STRING, `YTCOPR` DECIMAL(38,10), `YTGLBN` DECIMAL(38,10), `YTWCEX` DECIMAL(38,10), `YTGLEX` DECIMAL(38,10), `YTLDID` STRING, `YTLDED` INT, `YTTTAP` STRING, `YTINVA` STRING, `YTINRA` STRING, `YTINEA` STRING, `YTCRFL` STRING, `YTCPTR` DECIMAL(38,10), `YTPCOR` STRING, `YTAUSPTWW` STRING, `YTAUBP` STRING, `syncDateTime` TIMESTAMP, `syncOperation` STRING, `syncFlag` STRING ) USING DELTA

Re: Microsoft Azure Databricks Delta ConnectionTimeout

john_wang — Tue, 28 Sep 2021 02:56:45 GMT

Hello @charlesrinaldini_colas ,

Thanks for the files.

1. You are running build 356 . we have a relevant fix in build 514:

Databricks Delta Target When replicating large tables, Full Load would sometimes fail with a query timeout error.

Please upgrade to 7.0 SR4 (You could download it from Qlik download site, file name AttunityReplicate_7.0.0.555_X64.zip).

2. From the task log file lines 151 and 152 we see the load stage timeout after 10 minutes. However you set the CSV files limit to 1G (1000M, the default is 100M). Let's set it to smaller size, 100M or even 10M to make sure the issue is a pure timeout issue first.

3. I noticed you set the 2 timeout parameter to zero, I'm afraid it's not a good practice. let's set them to 1800 for our troubleshooting (we may adjust them later).

4. Let us know if the problem occurs for all tables, or for wide/large table only (the sample table has 152 columns).

5. If the problem persists still, let's open a support case and provide Diag Package with target_load/target_apply set to Verbose, and also the source side table creation DDL (you pasted a target side creation DDL). Note that attaching Verbose task log file here may expose some sensitive information to public that's why a support case is recommended.

I hope the above was helpful.

Thanks,

John.

Re: Microsoft Azure Databricks Delta ConnectionTimeout

charlesrinaldini_colas — Tue, 28 Sep 2021 18:12:39 GMT

@john_wang We only see version SR3 and the Attunity Replicate 7.0.0.555 is dated Nov 2020.

Re: Microsoft Azure Databricks Delta ConnectionTimeout

john_wang — Wed, 29 Sep 2021 00:43:56 GMT

Hello @charlesrinaldini_colas ,

"November 2020" is the major release version number (in former it's "7.0") , build 555 is released in April 2021. (it's SR4 rather than SR3. however does not matter the SR number, if the build number is 555 then it's correct one).

In the sample below 1) is for Linux and 2) is for Windows.

Let me know if you need any additional information.

Thanks,

John.