Create External Table Query Taking Long Time

Question

Create External Table Query Taking Long Time

Palash Aich 21

Hello there,

I downloaded SQL Server 2019 evaluation version and configured PolyBase to connect ADLS Gen 2 storage account.
I enabled PolyBase, created external data sources and file format. While trying to create external table, it is taking huge time. In fact the query keeps running and never succedded. Can you please suggest where to tweak.

Please refer screenshot.

Thanks,
Palash

Vaibhav Chaudhari 38,916 Reputation points Volunteer Moderator

2021-04-03T05:03:07.343+00:00

Do you have the CSV directly in container? Maybe try removing first slash / in the location
Palash Aich 21 Reputation points

2021-04-03T06:22:47.027+00:00

Thanks @Vaibhav Chaudhari

Please refer the screenshot for container structure.

I am using below Query to create external source and file format.

CREATE EXTERNAL DATA SOURCE AzureStorage WITH
(
TYPE = HADOOP,
LOCATION = 'wasbs://******@salesplatformcds2.blob.core.windows.net',
CREDENTIAL = AzurePolyBase
);

CREATE EXTERNAL FILE FORMAT TextFile
WITH (
FORMAT_TYPE = DelimitedText,
FORMAT_OPTIONS (FIELD_TERMINATOR = ',')
);

Do I need to make any change?
Vaibhav Chaudhari 38,916 Reputation points Volunteer Moderator

2021-04-03T07:22:20.483+00:00

Are both polybase services running?
Palash Aich 21 Reputation points

2021-04-03T15:26:24.55+00:00

Thanks @Vaibhav Chaudhari

Both the services are running. I cancelled the query a few times as it was taking time. For last execution I did not cancel it and after almost 1 and half hour I got below error. How do I make this dll available and should it be in my local system where SQL server is running?

Error occurred while accessing HDFS: An unexpected error has occurred while creating or attaching to JVM. Details: System.DllNotFoundException: Unable to load DLL 'jvm.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)
at JNI.JavaVM.JNI_GetCreatedJavaVMs(IntPtr& pVM, Int32 jSize1, Int32& jSize2)
at Microsoft.SqlServer.DataWarehouse.Hadoop.HadoopBridge.JavaBridge.LoadOrAttachJVM()
Vaibhav Chaudhari 38,916 Reputation points Volunteer Moderator

2021-04-04T06:47:27.7+00:00

Try installing Java JRE from here - https://www.java.com/en/download/manual.jsp

I saw similar error in the installation steps here -

https://social.technet.microsoft.com/wiki/contents/articles/53533.sql-server-2019-import-data-from-csv-in-azure-blob-storage-using-t-sql-and-polybase.aspx
Palash Aich 21 Reputation points

2021-04-05T06:48:45.967+00:00

Hi @Vaibhav Chaudhari

I followed the steps to install JVM. Also, post installation I verified JVM availability in my system where SQL server is installed. Please refer screen shot of JVM. I restarted the system to check if that works, however, still the same error. Can you please suggest if I need to modify any other settings?

Thanks,
Palash
Vaibhav Chaudhari 38,916 Reputation points Volunteer Moderator

2021-04-05T06:51:04.03+00:00

I have followed the same steps and creating external table works for me.

What's the error message you get when you create ext table now?
Palash Aich 21 Reputation points

2021-04-05T07:48:08.57+00:00

Hi @Vaibhav Chaudhari

Below is the error even after installing JVM.

Msg 110813, Level 16, State 1, Line 62
Error occurred while accessing HDFS: An unexpected error has occurred while creating or attaching to JVM. Details: System.DllNotFoundException: Unable to load DLL 'jvm.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)
at JNI.JavaVM.JNI_GetCreatedJavaVMs(IntPtr& pVM, Int32 jSize1, Int32& jSize2)
at Microsoft.SqlServer.DataWarehouse.Hadoop.HadoopBridge.JavaBridge.LoadOrAttachJVM()

Thanks,
Palash
Palash Aich 21 Reputation points

2021-04-05T15:29:32.35+00:00

Hi @Vaibhav Chaudhari

I reinstalled the PolyBase. This started working fine now.

I can connect to ADLS Gen 2 container and query the data available in csv file. As a next step, I need to check how to point to CDM in place of single CSV file.

Thanks for your help!

Your answer

Vaibhav Chaudhari 38,916 Reputation points Volunteer Moderator

2021-04-03T05:03:07.343+00:00

Do you have the CSV directly in container? Maybe try removing first slash / in the location
Palash Aich 21 Reputation points

2021-04-03T06:22:47.027+00:00

Thanks @Vaibhav Chaudhari

Please refer the screenshot for container structure.

I am using below Query to create external source and file format.

CREATE EXTERNAL DATA SOURCE AzureStorage WITH
(
TYPE = HADOOP,
LOCATION = 'wasbs://******@salesplatformcds2.blob.core.windows.net',
CREDENTIAL = AzurePolyBase
);

CREATE EXTERNAL FILE FORMAT TextFile
WITH (
FORMAT_TYPE = DelimitedText,
FORMAT_OPTIONS (FIELD_TERMINATOR = ',')
);

Do I need to make any change?
Vaibhav Chaudhari 38,916 Reputation points Volunteer Moderator

2021-04-03T07:22:20.483+00:00

Are both polybase services running?
Palash Aich 21 Reputation points

2021-04-03T15:26:24.55+00:00

Thanks @Vaibhav Chaudhari

Both the services are running. I cancelled the query a few times as it was taking time. For last execution I did not cancel it and after almost 1 and half hour I got below error. How do I make this dll available and should it be in my local system where SQL server is running?

Error occurred while accessing HDFS: An unexpected error has occurred while creating or attaching to JVM. Details: System.DllNotFoundException: Unable to load DLL 'jvm.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)
at JNI.JavaVM.JNI_GetCreatedJavaVMs(IntPtr& pVM, Int32 jSize1, Int32& jSize2)
at Microsoft.SqlServer.DataWarehouse.Hadoop.HadoopBridge.JavaBridge.LoadOrAttachJVM()
Vaibhav Chaudhari 38,916 Reputation points Volunteer Moderator

2021-04-04T06:47:27.7+00:00

Try installing Java JRE from here - https://www.java.com/en/download/manual.jsp

I saw similar error in the installation steps here -

https://social.technet.microsoft.com/wiki/contents/articles/53533.sql-server-2019-import-data-from-csv-in-azure-blob-storage-using-t-sql-and-polybase.aspx
Palash Aich 21 Reputation points

2021-04-05T06:48:45.967+00:00

Hi @Vaibhav Chaudhari

I followed the steps to install JVM. Also, post installation I verified JVM availability in my system where SQL server is installed. Please refer screen shot of JVM. I restarted the system to check if that works, however, still the same error. Can you please suggest if I need to modify any other settings?

Thanks,
Palash
Vaibhav Chaudhari 38,916 Reputation points Volunteer Moderator

2021-04-05T06:51:04.03+00:00

I have followed the same steps and creating external table works for me.

What's the error message you get when you create ext table now?
Palash Aich 21 Reputation points

2021-04-05T07:48:08.57+00:00

Hi @Vaibhav Chaudhari

Below is the error even after installing JVM.

Msg 110813, Level 16, State 1, Line 62
Error occurred while accessing HDFS: An unexpected error has occurred while creating or attaching to JVM. Details: System.DllNotFoundException: Unable to load DLL 'jvm.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)
at JNI.JavaVM.JNI_GetCreatedJavaVMs(IntPtr& pVM, Int32 jSize1, Int32& jSize2)
at Microsoft.SqlServer.DataWarehouse.Hadoop.HadoopBridge.JavaBridge.LoadOrAttachJVM()

Thanks,
Palash
Palash Aich 21 Reputation points

2021-04-05T15:29:32.35+00:00

Hi @Vaibhav Chaudhari

I reinstalled the PolyBase. This started working fine now.

I can connect to ADLS Gen 2 container and query the data available in csv file. As a next step, I need to check how to point to CDM in place of single CSV file.

Thanks for your help!

Share via

Create External Table Query Taking Long Time

Your answer