Common Data Loading Error Messages¶
Loading data into Actian Data Platform involves the following steps:
- Creating the external table that references data on your data store.
-
Creating the native Actian Data Platform table.
-
Inserting data from the external table reference to the native Actian Data Platform table.
- Creating stats on the Actian Data Platform table to increase performance.
Getting everything correct in these steps can be challenging, since the schema and data need to match exactly. Creating the external table sometimes results in issues during the loading step. The following examples list some of the error messages you may encounter.
SQL Error [2014] [42000]
SQL Error [2014] [42000]: CREATE or ALTER TABLE: invalid column format 'integerr' on column 'c_custkey'.
Code:
) using spark with reference='s3a://<bucket_name>/customer.tbl*',format='csv',options=('header'='true','delimiter'='|');
Reason and correction:
Invalid data type. Datatype “INTEGERR” is misspelled. Correct the datatype to “INTEGER”.
SQL Error [3809] [42000]
SQL Error [3809] [42000]: Line 3, syntax error on 'c_name'. The correct syntax is: CREATE EXTERNAL TABLE table_name ( column_name col_type {, ... } ) USING [schema.]provider_name WITH REFERENCE = 'reference-value' [,FORMAT = 'format-value'] [,EXTERNAL_ROLE = 'external-role'] [,OPTIONS = ('key'= 'value' {,'key'= 'value'})] [,ROWS = integer_value ]
Code:
) using spark with reference='s3a://<bucket_name>/customer.tbl*',format='csv',options=('header'='true','delimiter'='|');
Reason and correction:
Invalid format (without comma). Insert comma after “INTEGER” on line 2.
SQL Error [1315347] [5000A] – Example 1
SQL Error [1315347] [5000A]: External table provider reported an error 'java.io.FileNotFoundException: Bucket some-junk-path does not exist'. For more information: check ${II_SYSTEM}/ingres/files/spark_provider.log, query id 56, transaction id 668.
Code:
) using spark with reference='s3a://<bucket_name>/customer.tbl*',format='csv',options=('header'='true','delimiter'='|');
Reason and correction:
Invalid bucket location (last 2 lines). Enter a valid path to the S3 bucket.
SQL Error [1315347] [5000A] – Example 2
SQL Error [1315347] [5000A]: External table provider reported an error 'org.apache.spark.sql.AnalysisException: Path does not exist: s3a://
Code:
) using spark with reference='s3a://<bucket_name>/customers.tbl*',format='csv',options=('header'='true','delimiter'='|');
Reason and correction:
Invalid reference path. Correct the path.
SQL Error [1315347] [5000A] – Example 3
SQL Error [1315347] [5000A]: External table provider reported an error 'java.lang.ClassNotFoundException: Failed to find data source: csvv. Please find packages at http://spark.apache.org/third-party-projects.html, caused by: java.lang.ClassNotFoundException: csvv.DefaultSource'. For more information: check ${II_SYSTEM}/ingres/files/spark_provider.log, query id 116, transaction id 698.
Code:
) using spark with reference='s3a://<bucket_name>/customer.tbl*',format='csvv',options=('header'='true','delimiter'='|');
Reason and correction:
Invalid format. Correct ‘csvv’ to ‘csv’ on the last line.
SQL Error [1315347] [5000A] – Example 4
SQL Error [1315347] [5000A]: External table provider reported an error 'java.lang.Exception: header flag can be true or false'. For more information: check ${II_SYSTEM}/ingres/files/spark_provider.log, query id 134, transaction id 706.
Code:
) using spark with reference='s3a://<bucket_name>/customer.tbl*',format='csv',options=('header'='truee','delimiter'='|');
Reason and correction:
Invalid header flag. Correct ‘truee’ to ‘true’ on the last line.
SQL Error [1315347] [5000A] – Example 5
SQL Error [1315347] [5000A]: External table provider reported an error 'org.apache.spark.sql.AnalysisException: cannot resolve 'c_custkey' given input columns: [src_5._c4, src_5._c0, src_5._c5, src_5._c6, src_5._c2, src_5._c3, src_5._c1, src_5._c7]; line 1 pos 41;
'InsertIntoTable 'UnresolvedRelation customer_s3_6, false, false
+- 'Project ['c_custkey, 'c_name, 'c_address, 'c_nationkey, 'c_phone, 'c_acctbal, 'c_mktsegment, 'c_comment]
+- SubqueryAlias src_5
+- Relation[_c0#166,_c1#167,_c2#168,_c3#169,_c4#170,_c5#171,_c6#172,_c7#173] csv'. For more information: check ${II_SYSTEM}/ingres/files/spark_provider.log, query id 150, transaction id 713.
Code:
) using spark with reference='s3a://<bucket_name>/customer.tbl*',format='csv',options=('heade'='true','delimiter'='|');
Reason and correction:
Invalid header field. Correct ‘heade’ to ‘header’ on last line.
SQL Error [1315347] [5000A] – Example 6
SQL Error [1315347] [5000A]: External table provider reported an error 'org.apache.spark.sql.AnalysisException: cannot resolve 'c_custkey' given input columns: [src_7.c_custkey|c_name|c_address|c_nationkey|c_phone|c_acctbal|c_mktsegment|c_comment]; line 1 pos 41;
'InsertIntoTable 'UnresolvedRelation customer_s3_8, false, false
+- 'Project ['c_custkey, 'c_name, 'c_address, 'c_nationkey, 'c_phone, 'c_acctbal, 'c_mktsegment, 'c_comment]
+- SubqueryAlias src_7
+- Relation[c_custkey|c_name|c_address|c_nationkey|c_phone|c_acctbal|c_mktsegment|c_comment#208] csv'. For more information: check ${II_SYSTEM}/ingres/files/spark_provider.log, query id 166, transaction id 720.
Code:
) using spark with reference='s3a://<bucket_name>/customer.tbl*',format='csv',options=('header'='true','delimiter'='!');
Reason and correction:
Invalid delimiter. Enter a valid delimiter character on the last line.
SQL Error [1315347] [5000A] – Example 7
SQL Error [1315347] [5000A]: External table provider reported an error 'org.apache.spark.sql.AnalysisException: cannot resolve 'c_custkey' given input columns: [src_9.c_custkey|c_name|c_address|c_nationkey|c_phone|c_acctbal|c_mktsegment|c_comment]; line 1 pos 42;
'InsertIntoTable 'UnresolvedRelation customer_s3_10, false, false
+- 'Project ['c_custkey, 'c_name, 'c_address, 'c_nationkey, 'c_phone, 'c_acctbal, 'c_mktsegment, 'c_comment]
+- SubqueryAlias src_9
+- Relation[c_custkey|c_name|c_address|c_nationkey|c_phone|c_acctbal|c_mktsegment|c_comment#236] csv'. For more information: check ${II_SYSTEM}/ingres/files/spark_provider.log, query id 184, transaction id 728.
Code:
) using spark with reference='s3a://<bucket_name>/customer.tbl*',format='csv',options=('header'='true','delimite'='|');
Reason and correction:
Invalid delimiter field name. Correct ‘delimite’ to ‘delimiter’ on last line.
SQL Error [1315347] [5000A] – Example 8
SQL Error [1315347] [5000A]: External table provider reported an error 'org.apache.spark.sql.AnalysisException: cannot resolve 'c_custkeyy' given input columns: [src_11.c_name, src_11.c_comment, src_11.c_address, src_11.c_custkey, src_11.c_nationkey, src_11.c_mktsegment, src_11.c_phone, src_11.c_acctbal]; line 1 pos 42;
'InsertIntoTable 'UnresolvedRelation customer_s3_12, false, false
+- 'Project ['c_custkeyy, c_name#265, c_address#266, c_nationkey#267, c_phone#268, c_acctbal#269, c_mktsegment#270, c_comment#271]
+- SubqueryAlias src_11
+- Relation[c_custkey#264,c_name#265,c_address#266,c_nationkey#267,c_phone#268,c_acctbal#269,c_mktsegment#270,c_comment#271] csv'. For more information: check ${II_SYSTEM}/ingres/files/spark_provider.log, query id 208, transaction id 739.
Code:
) using spark with reference='s3a://<bucket_name>/customer.tbl*',format='csv',options=('header'='true','delimiter'='|');
Reason and correction:
Spark schema mapping not correctly. Ensure declared column name (c_custkey) matches schema.
SQL Error [1315347] [5000A] – Example 9
SQL Error [1315347] [5000A]: External table provider reported an error 'org.apache.spark.sql.AnalysisException: cannot resolve 'c_custkey' given input columns: [src_15._c0, src_15._c1, src_15._c3, src_15._c6, src_15._c5, src_15._c7, src_15._c4, src_15._c2]; line 1 pos 42;
'InsertIntoTable 'UnresolvedRelation customer_s3_16, false, false
+- 'Project ['c_custkey, 'c_name, 'c_address, 'c_nationkey, 'c_phone, 'c_acctbal, 'c_mktsegment, 'c_comment]
+- SubqueryAlias src_15
+- Relation[_c0#348,_c1#349,_c2#350,_c3#351,_c4#352,_c5#353,_c6#354,_c7#355] csv'. For more information: check ${II_SYSTEM}/ingres/files/spark_provider.log, query id 244, transaction id 755.
Code:
) using spark with reference='s3a://<bucket_name>/customer.tbl*',format='csv',options=('header'='false','delimiter'='|');
Reason and correction:
Data has a header but ‘false’ was provided in the query. Change to ‘true’.
SQL Error [1315347] [5000A] – Example 10
SQL Error [1315347] [5000A]: External table provider reported an error 'org.apache.hadoop.fs.s3a.AWSS3IOException: getFileStatus on s3a://
Code:
) using spark with reference='s3a://<bucket_name>/customer.tbl.1.csv',format='csv',options=('header'='false','delimiter'='|');
Reason and correction:
S3 permissions are not set for the warehouse. Set S3 permissions. See Set Up Access Credentials for Cloud Storage Accounts.
SQL Error [1315347] [5000A] – Example 11
SQL Error [1315347] [5000A]: External table provider reported an error 'org.apache.hadoop.fs.s3a.AWSS3IOException: getFileStatus on s3a://
Code:
) using spark with reference='s3a://<bucket_name>/customer.tbl.1.csv',format='csv',options=('header'='false','delimiter'='|');
Reason and correction:
The warehouse is in N.Virginia but data is being loaded from Ohio region
SQL Error [1315347] [5000A] – Example 12
SQL Error [1315347] [5000A]: External table provider reported an error 'java.nio.file.AccessDeniedException: : getFileStatus on : com.amazonaws.services.s3.model.AmazonS3Exception: Access Denied (Service: Amazon S3; Status Code: 403; Error Code: AccessDenied; Request ID: 87559381F3F15DA7), S3 Extended Request ID: hSfwo/ABeykNkjcpB+Rz0DcXiHDN5qI5z4ELP94hSNEnqUVHbAx9xdvKP+STq5R4IqTvskwr6KM=, caused by: com.amazonaws.services.s3.model.AmazonS3Exception: Access Denied (Service: Amazon S3; Status Code: 403; Error Code: AccessDenied; Request ID: 87559381F3F15DA7), S3 Extended Request ID: hSfwo/ABeykNkjcpB+Rz0DcXiHDN5qI5z4ELP94hSNEnqUVHbAx9xdvKP+STq5R4IqTvskwr6KM='. For more information: check ${II_SYSTEM}/ingres/files/spark_provider.log, query id 310, transaction id 785.
Code:
) using spark with reference='s3a://<bucket_name>/customer.tbl*',format='csv',options=('header'='false','delimiter'='|');
Reason and correction:
S3 is set with a different environment’s credentials. Use the correct credentials.
SQL Error [1315347] [5000A] – Example 13
SQL Error [1315347] [5000A]: External table provider reported an error 'org.apache.spark.sql.AnalysisException: cannot resolve 'c_custkey' given input columns: [src_1.1 Customer#000000001 j5JsirBM9PsCy0O1m 15 25-989-741-2988 711.56 BUILDING "regular, regular platelets are fluffily according to the even attainments. blithely iron"]; line 1 pos 41;
'InsertIntoTable 'UnresolvedRelation customer_s3_2, false, false
+- 'Project ['c_custkey, 'c_name, 'c_address, 'c_nationkey, 'c_phone, 'c_acctbal, 'c_mktsegment, 'c_comment]
+- SubqueryAlias src_1
+- Relation[1 Customer#000000001 j5JsirBM9PsCy0O1m 15 25-989-741-2988 711.56 BUILDING "regular, regular platelets are fluffily according to the even attainments. blithely iron"#10] csv'. For more information: check ${II_SYSTEM}/ingres/files/spark_provider.log, query id 346, transaction id 801.
Code:
) using spark with reference='s3a://<bucket_name>/customer.tbl.1',format='csv',options=('header'='true','delimiter'='|');
Reason and correction:
Header is set to “true” when data has no header. Set header to ‘false’.