question Re: Inserting From external Data Table to Hive Table in Archives of Support Questions (Read Only)

Inserting From external Data Table to Hive Table

oula_alshiekh — Tue, 13 Dec 2016 22:09:35 GMT

when we use PolyBase which is sql server 2016 technique and add an external table to a table in hive and we want to insert data in this external table =>inserting this data in associated hive table my question is ? is there any limit in external table max inserted records i mean if iam inserting data in external table from another sql server table that has more than 30000 records i encounter this error

Cannot execute the query "Remote Query" against OLE DB provider "SQLNCLI11" for linked server "SQLNCLI11". 110802;An internal DMS error occurred that caused this operation to fail. Details: Exception: Microsoft.SqlServer.DataWarehouse.DataMovement.Common.ExternalAccess.HdfsAccessException, Message: Java exception raised on call to HdfsBridge_DestroyRecordWriter: Error [0 at org.apache.hadoop.hdfs.server.blockmanagement.DatanodeManager.getDatanodeStorageInfos(DatanodeManager.java:513) at org.apache.hadoop.hdfs.server.namenode.FSNamesystem.updatePipelineInternal(FSNamesystem.java:6379) at org.apache.hadoop.hdfs.server.namenode.FSNamesystem.updatePipeline(FSNamesystem.java:6344) at org.apache.hadoop.hdfs.server.namenode.NameNodeRpcServer.updatePipeline(NameNodeRpcServer.java:822) at org.apache.hadoop.hdfs.protocolPB.ClientNamenodeProtocolServerSideTranslatorPB.updatePipeline(ClientNamenodeProtocolServerSideTranslatorPB.java:971) at org.apache.hadoop.hdfs.protocol.proto.ClientNamenodeProtocolProtos$ClientNamenodeProtocol$2.callBlockingMethod(ClientNamenodeProtocolProtos.java) at org.apache.hadoop.ipc.ProtobufRpcEngine$Server$ProtoBufRpcInvoker.call(ProtobufRpcEngine.java:616) at org.apache.hadoop.ipc.RPC$Server.call(RPC.java:969) at org.apache.hadoop.ipc.Server$Handler$1.run(Server.java:2049) at org.apache.hadoop.ipc.Server$Handler$1.run(Server.java:2045) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAs(Subject.java:415) at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1656) at org.apache.hadoop.ipc.Server$Handler.run(Server.java:2043) ] occurred while accessing external file.

while inserting less than 30000 records leads to every thing works ok and data is inserted in hive will this error becuase of one of the reasons

1- there is a limit in external table insert records number

2- there is a limit in poly base configuration

3- Any other problem in hive

Re: Inserting From external Data Table to Hive Table

mqureshi — Tue, 13 Dec 2016 23:32:52 GMT

@oula.alshiekh@gmail.com alshiekh

When you say that you are able to insert less than 30K records, does that mean the same source and destination? The reason I ask is because your error points towards a permission/access issue.

Re: Inserting From external Data Table to Hive Table

TimothySpann — Tue, 13 Dec 2016 23:34:48 GMT

Polybase Configuration Issue it seems

https://msdn.microsoft.com/en-us/library/dn935026.aspx

The maximum number of concurrent PolyBase queries is 32. When 32 concurrent queries are running, each query can read a maximum of 33,000 files from the external file location. The root folder and each subfolder also count as a file. If the degree of concurrency is less than 32, the external file location can contain more than 33,000 files.

Is this a single file or multiple files you are accessing. What is the Hive DDL?

I would recommend Hive with ORC format and fewer files.

See:

https://sqlwithmanoj.com/2016/06/09/polybase-error-in-sql-server-2016-row-size-exceeds-the-defined-maximum-dms-row-size-larger-than-the-limit-of-32768-bytes/

https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-service-capacity-limits

Loads

Category	Description	Maximum
Polybase Loads	Bytes per row	32,768 Polybase loads are limited to loading rows both smaller than 32K and cannot load to VARCHR(MAX), NVARCHAR(MAX) or VARBINARY(MAX). While this limit exists today, it will be removed fairly soon.

see: https://blogs.msdn.microsoft.com/sqlcat/2016/06/21/polybase-setup-errors-and-possible-solutions/

Check for errors on the Hadoop server.

Which version of HDP or HDInsight? Hive version? Is there any memory issues?

Check logs and ambari.

Re: Inserting From external Data Table to Hive Table

oula_alshiekh — Wed, 14 Dec 2016 17:48:34 GMT

source sql server table ,destination is hive table

i haven't configured any permissions configurations in hadoop yet

so my problem is because of polybase limited inserted rows

thanks for you

Re: Inserting From external Data Table to Hive Table

oula_alshiekh — Wed, 14 Dec 2016 17:49:17 GMT

thanks for your brilliant detailed answer

Re: Inserting From external Data Table to Hive Table

jose_agc — Sat, 07 Jan 2017 03:15:35 GMT

There are people who instead of helping what they do is confusing. This is a straight answer to the point. Congratulations and thank you.