Azure Data Lake Storage
An Azure service that provides an enterprise-wide hyper-scale repository for big data analytic workloads and is integrated with Azure Blob Storage.
Create External Table Query Taking Long Time
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(227.2, 5%, 32%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EPA%3C/text%3E%3C/svg%3E)
Palash Aich
21
Reputation points
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
Azure Data Lake Storage
Developer technologies | Transact-SQL
Developer technologies | Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
SQL Server | Other
{count} votes
-
Vaibhav Chaudhari 38,971 Reputation points Volunteer Moderator2021-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,971 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,971 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 -
-
Palash Aich 21 Reputation points
2021-04-05T06:48:45.967+00:00 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,971 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 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 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!
Sign in to comment
Sign in to answer