topic Re: Databricks Lakehouse (Delta) Target connection not working in DRP server. in Qlik Replicate

Databricks Lakehouse (Delta) Target connection not working in DRP server.

SaidSayyad — Fri, 29 Sep 2023 10:49:08 GMT

Hello Team,

we are using Databricks Lakehouse (Delta) as a target. while setup connection we are observing below error.

we tried to uninstall & re-install Simba Sparks driver version simbaspark-2.6.26.1045-1.x86_64.rpm. we also updated Simba file but still facing same error.

Update file /opt/simba/spark/Setup/odbcinst.ini

Add lines :

# Driver from the SimbaSparkODBC-2.6.29.1049-LinuxRPM-64bit package

# Setup from the unixODBC package

[Simba Spark ODBC Driver]

Description = Simba Apache Spark ODBC Connector (64-bit)

Driver = /opt/simba/spark/lib/64/libsparkodbc_sb64.so

Test connection Error:

Failed prepare Cloud component
Cannot connect to Cloud server RetCode: SQL_ERROR SqlState: 01000 NativeError: 0 Message: [unixODBC][Driver Manager]Can't open lib 'Simba Spark ODBC Driver' : file not found

Logfile error:

01958621: 2023-09-29T10:32:27:706457 [STREAM_COMPONEN ]I: Going to connect to server westeurope.azuredatabricks.net database (null) (cloud_imp.c:3883)

01958621: 2023-09-29T10:32:27:706497 [STREAM_COMPONEN ]I: Target endpoint 'Databricks Lakehouse (Delta)' is using provider syntax 'DatabricksDelta' (provider_syntax_manager.c:894)

01958621: 2023-09-29T10:32:27:719774 [STREAM_COMPONEN ]E: RetCode: SQL_ERROR SqlState: 01000 NativeError: 0 Message: [unixODBC][Driver Manager]Can't open lib 'Simba Spark ODBC Driver' : file not found [1022502] (ar_odbc_conn.c:584)

01958621: 2023-09-29T10:32:27:719788 [STREAM_COMPONEN ]E: Cannot connect to Cloud server [1022501] (cloud_imp.c:3918)

01958621: 2023-09-29T10:32:27:719790 [STREAM_COMPONEN ]E: Failed prepare Cloud component [1022501] (cloud_metadata.c:239)

Thanks,

Said Sayyad

Re: Databricks Lakehouse (Delta) Target connection not working in DRP server.

john_wang — Fri, 29 Sep 2023 14:29:22 GMT

Hello @SaidSayyad ,

Thanks for reaching out to Qlik Community!

Looks to me this is an ODBC configuration issue. Let's troubleshoot the issue by steps:

1. Please merge below lines into "/etc/odbcinst.ini" file rather than using "/opt/simba/spark/Setup/odbcinst.ini"

[Simba Spark ODBC Driver]
Description = Simba Apache Spark ODBC Connector (64-bit)
Driver = /opt/simba/spark/lib/64/libsparkodbc_sb64.so

2. Add the PATH "/opt/simba/spark/lib/64/" to LD_LIBRARY_PATH environment variable in file "/opt/Attunity/replicate/bin/site_arep_login.sh" (default location). Make sure the Replicate account (by default the name is "attunity") has enough privileges to access/execute the Simba files.

3. Restart Qlik Replicate Service after the above step 2 is done.

4. If the problem persists, it's better to create 64-bit ODBC DSN in file "/etc/odbc.ini" and then use command "isql" to test the connectivity issue, it's much easier than doing it by Replicate. If "isql" works fine then Replicate should work as well.

5. Link from Databricks: Troubleshooting JDBC and ODBC connections

Hope this helps.

Regards,

John.

Re: Databricks Lakehouse (Delta) Target connection not working in DRP server.

SaidSayyad — Fri, 06 Oct 2023 08:46:53 GMT

Hello John,
I have performed all steps provide by you but still facing same issue. Please find attached snapshot for your reference.

Thanks,
Said Sayyad

Re: Databricks Lakehouse (Delta) Target connection not working in DRP server.

SaidSayyad — Fri, 06 Oct 2023 08:53:11 GMT

Re: Databricks Lakehouse (Delta) Target connection not working in DRP server.

john_wang — Sat, 07 Oct 2023 14:24:09 GMT

Hi @SaidSayyad ,

Thanks for the information.

The default driver name is "Simba Spark ODBC Driver". In your "odbcinst.ini" file, the driver name is "Simba Spark ODBC Driver 64-bit". So far you can either rename the driver name in "odbcinst.ini" file to default name, or you can use the predefined name in Databricks target endpoint, as below:

Open Databricks target endpoint
Go to the Advanced tab
Go to the Internal properties
Add parameter name "driver" in the input label, and set its value to "Simba Spark ODBC Driver 64-bit".

If the problem persists, please try to use "isql" to test the connectivity issue.

BTW, it seems the Samba folder was not added into LD_LIBRARY_PATH environment variable correctly: the line is commented out. please use "env|grep LD_LIBRARY_PATH " to check if it's set correctly.

Regards,

John.

Re: Databricks Lakehouse (Delta) Target connection not working in DRP server.

SaidSayyad — Mon, 09 Oct 2023 08:26:53 GMT

Thank you John,

Now connection is working fine, you can mark this case as close.

Thanks,

Said Sayyad

Re: Databricks Lakehouse (Delta) Target connection not working in DRP server.

SushilKumar — Mon, 09 Oct 2023 08:50:12 GMT

Hello team,

If our response has been helpful, please consider clicking "Accept as Solution". This will assist other users in easily finding the answer.

Regards,

Sushil Kumar

Re: Databricks Lakehouse (Delta) Target connection not working in DRP server.

john_wang — Mon, 09 Oct 2023 08:53:05 GMT

Hello @SaidSayyad ,

Thank you so much for your feedback.

Do you mind sharing with us the final solution? It's very helpful for all of us.

Best Regards,

John.

Re: Databricks Lakehouse (Delta) Target connection not working in DRP server.

SaidSayyad — Tue, 10 Oct 2023 13:06:56 GMT

Hello John,

As per your suggestion we have changed driver name under "odbcinst.ini" file and it's working now.

Thanks,

Said Sayyad

Re: Databricks Lakehouse (Delta) Target connection not working in DRP server.

john_wang — Tue, 10 Oct 2023 15:09:13 GMT

Hi @SaidSayyad ,

Glad to hear that and thanks for your update.

Regards,

John.

Re: Databricks Lakehouse (Delta) Target connection not working in DRP server.

nareshkumar — Wed, 21 Feb 2024 06:37:45 GMT

Hi John,

We tried the steps placed but still facing the same error #

Failed prepare Cloud component
Cannot connect to Cloud server RetCode: SQL_ERROR SqlState: 01000 NativeError: 0 Message: [unixODBC][Driver Manager]Can't open lib 'Simba Spark ODBC Driver 64-bit' : file not found

tried with both :

[Simba Spark ODBC Drivert]
Description=Amazon Hive ODBC Driver (64-bit)
Driver=/opt/simba/spark/lib/64/libsparkodbc_sb64.so

[Simba Spark ODBC Driver 64-bit]
Description=Amazon Hive ODBC Driver (64-bit)
Driver=/opt/simba/spark/lib/64/libsparkodbc_sb64.so

added the internal parameter driver ...

-> also added the paths to site_arep_login.sh:

#Delta Databricks
export ODBCINI=/etc/odbc.ini
export ODBCSYSINI=/etc/odbc
export SPARKINI=/etc/simba.sparkodbc.ini
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/simba/spark/lib/64/libsparkodbc_sb64.so

Copied simba.sparkodbc.ini file to /etc location

restarted the services but still getting the same error .. Please help me.

Re: Databricks Lakehouse (Delta) Target connection not working in DRP server.

john_wang — Wed, 21 Feb 2024 06:46:03 GMT

Hello @nareshkumar ,

Noticed the line:

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/simba/spark/lib/64/libsparkodbc_sb64.so

This is inaccurate as we only need the DLL folder name rather than the DLL file name. Please change it to:

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/simba/spark/lib/64/

Hope it works for you.

John.

Re: Databricks Lakehouse (Delta) Target connection not working in DRP server.

nareshkumar — Wed, 21 Feb 2024 06:55:03 GMT

Thank you So much for quick response and tried updating the export path to

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/simba/spark/lib/64/

sourced siterep file and restarted the service and still getting the same error.

Re: Databricks Lakehouse (Delta) Target connection not working in DRP server.

john_wang — Wed, 21 Feb 2024 07:17:43 GMT

Hello @nareshkumar ,

Let's make things simple. Please use the "isql" command to troubleshoot the ODBC DSN connectivity issue rather than doing that in Qlik Replicate.

Let's try below steps:

1. Change to use "attunity" (or other account which you choosed)

2. make sure "/etc/odbcinst.ini" is there and configured correctly

3. make sure "/etc/odbc.ini" is there and configured correctly

4. run "source ./arep_login.sh"

5. run "isql" command to access the DSN. If the unixODBC is not installed or not configured correctly, please follow the steps to install it (by root or other privileged account).

In general this error is out of Qlik Replicate, if isql works then Replicate should work too.

Regards,

John.

Re: Databricks Lakehouse (Delta) Target connection not working in DRP server.

SushilKumar — Thu, 22 Feb 2024 11:11:50 GMT

Hello team,

If our response has been helpful, please consider clicking "Accept as Solution". This will assist other users in easily finding the answer.

Regards,

Sushil Kumar

Re: Databricks Lakehouse (Delta) Target connection not working in DRP server.

nareshkumar — Thu, 22 Feb 2024 14:24:23 GMT

Apologies for the delayed response ...the solution not working for us .. in previous reply from John it is stated that it is out of replicate . So didn't responded ..

Re: Databricks Lakehouse (Delta) Target connection not working in DRP server.

nareshkumar — Thu, 22 Feb 2024 14:26:05 GMT

could you please confirm that AWS databriks lakehouse (delta) is supported by Qlik or only Azure will support ?

Re: Databricks Lakehouse (Delta) Target connection not working in DRP server.

Dana_Baldwin — Thu, 22 Feb 2024 17:48:48 GMT

Hi @nareshkumar

Unfortunately, our documentation does not specify which platform is supported for Databricks Lakehouse Delta. This suggests it is supported for either, but I cannot say with certainty.

Supported target endpoints #Supported target endpoints | Qlik Replicate Help

I'll ask and follow up. We don't have a way to elevate questions/issues to our internal support team via the Community, so we may need a support case for this question.

Thanks,

Dana

Re: Databricks Lakehouse (Delta) Target connection not working in DRP server.

Dana_Baldwin — Thu, 22 Feb 2024 18:23:57 GMT

Hi @nareshkumar

Yes, we support Databricks Lakehouse Delta on both AWS and Azure.

Thanks,

Dana

Re: Databricks Lakehouse (Delta) Target connection not working in DRP server.

nareshkumar — Sat, 24 Feb 2024 04:36:59 GMT

In order to check isql with DSN .. what are all the parameters to be part of DSN file .. we are using Token in the Replicate endpoint...

We are in Linux platform... Please help us in sharing sample parameters to setup a DSN in odbc.ini file ...

For AS 400 we are passing like below
[CAMS]
Description = DSN for CAMS.TMS.TOYOTA.COM
Driver = IBM i Access ODBC Driver
System = camsdb.localhost
SSL=1

For DB2 as follows:

[DSN_TRG]
Description = DSN for DB2 cp1fa.vipa.tmm.toyota.com
Driver = IBM DB2 ODBC DRIVER

Please help us with Databriks Lakehouse(Delta DSN ) details ...

[DSN_DELTA]
SSLTrustedCertsPath=/opt/simba/spark/lib/64/databriks.pem
SSL=1
UseSystemTrustStore=1