spark throws error when reading hive transaction table

Question

i am trying to do select * from db.abc in hive,this hive table was loaded using spark

it does not work shows an error:

Error: java.io.IOException: java.lang.IllegalArgumentException: bucketId out of range: -1 (state=,code=0)

when i use the following properties i was able to query for hive:

set hive.mapred.mode=nonstrict;
set hive.optimize.ppd=true;
set hive.optimize.index.filter=true;
set hive.tez.bucket.pruning=true;
set hive.explain.user=false; 
set hive.fetch.task.conversion=none;

now when i try to read the same hive table db.abc using spark , i am recieving the error as below:

Clients can access this table only if they have the following capabilities: CONNECTORREAD,HIVEFULLACIDREAD,HIVEFULLACIDWRITE,HIVEMANAGESTATS,HIVECACHEINVALIDATE,CONNECTORWRITE. This table may be a Hive-managed ACID table, or require some other capability that Spark currently does not implement; at org.apache.spark.sql.catalyst.catalog.CatalogUtils$.throwIfNoAccess(ExternalCatalogUtils.scala:280) at org.apache.spark.sql.hive.HiveTranslationLayerCheck$$anonfun$apply$1.applyOrElse(HiveTranslationLayerStrategies.scala:105) at org.apache.spark.sql.hive.HiveTranslationLayerCheck$$anonfun$apply$1.applyOrElse(HiveTranslationLayerStrategies.scala:85) at org.apache.spark.sql.catalyst.trees.TreeNode$$anonfun$transformUp$1.apply(TreeNode.scala:289) at org.apache.spark.sql.catalyst.trees.TreeNode$$anonfun$transformUp$1.apply(TreeNode.scala:289) at org.apache.spark.sql.catalyst.trees.CurrentOrigin$.withOrigin(TreeNode.scala:70) at org.apache.spark.sql.catalyst.trees.TreeNode.transformUp(TreeNode.scala:288) at org.apache.spark.sql.catalyst.trees.TreeNode$$anonfun$3.apply(TreeNode.scala:286) at org.apache.spark.sql.catalyst.trees.TreeNode$$anonfun$3.apply(TreeNode.scala:286) at org.apache.spark.sql.catalyst.trees.TreeNode$$anonfun$4.apply(TreeNode.scala:306) at org.apache.spark.sql.catalyst.trees.TreeNode.mapProductIterator(TreeNode.scala:187) at org.apache.spark.sql.catalyst.trees.TreeNode.mapChildren(TreeNode.scala:304) at org.apache.spark.sql.catalyst.trees.TreeNode.transformUp(TreeNode.scala:286) at org.apache.spark.sql.catalyst.trees.TreeNode$$anonfun$3.apply(TreeNode.scala:286) at org.apache.spark.sql.catalyst.trees.TreeNode$$anonfun$3.apply(TreeNode.scala:286) at org.apache.spark.sql.catalyst.trees.TreeNode$$anonfun$4.apply(TreeNode.scala:306) at org.apache.spark.sql.catalyst.trees.TreeNode.mapProductIterator(TreeNode.scala:187) at org.apache.spark.sql.catalyst.trees.TreeNode.mapChildren(TreeNode.scala:304) at org.apache.spark.sql.catalyst.trees.TreeNode.transformUp(TreeNode.scala:286) at org.apache.spark.sql.hive.HiveTranslationLayerCheck.apply(HiveTranslationLayerStrategies.scala:85) at org.apache.spark.sql.hive.HiveTranslationLayerCheck.apply(HiveTranslationLayerStrategies.scala:83) at org.apache.spark.sql.catalyst.rules.RuleExecutor$$anonfun$execute$1$$anonfun$apply$1.apply(RuleExecutor.scala:87) at org.apache.spark.sql.catalyst.rules.RuleExecutor$$anonfun$execute$1$$anonfun$apply$1.apply(RuleExecutor.scala:84) at scala.collection.LinearSeqOptimized$class.foldLeft(LinearSeqOptimized.scala:124) at scala.collection.immutable.List.foldLeft(List.scala:84) at org.apache.spark.sql.catalyst.rules.RuleExecutor$$anonfun$execute$1.apply(RuleExecutor.scala:84) at org.apache.spark.sql.catalyst.rules.RuleExecutor$$anonfun$execute$1.apply(RuleExecutor.scala:76) at scala.collection.immutable.List.foreach(List.scala:392) at org.apache.spark.sql.catalyst.rules.RuleExecutor.execute(RuleExecutor.scala:76) at org.apache.spark.sql.catalyst.analysis.Analyzer.org$apache$spark$sql$catalyst$analysis$Analyzer$$executeSameContext(Analyzer.scala:124) at org.apache.spark.sql.catalyst.analysis.Analyzer.execute(Analyzer.scala:118) at org.apache.spark.sql.catalyst.analysis.Analyzer.executeAndCheck(Analyzer.scala:103) at org.apache.spark.sql.execution.QueryExecution.analyzed$lzycompute(QueryExecution.scala:57) at org.apache.spark.sql.execution.QueryExecution.analyzed(QueryExecution.scala:55) at org.apache.spark.sql.execution.QueryExecution.assertAnalyzed(QueryExecution.scala:47) at org.apache.spark.sql.Dataset$.ofRows(Dataset.scala:74) at org.apache.spark.sql.SparkSession.sql(SparkSession.scala:642) ... 49 elided

do i need to add any properties in spark-submit or shell ? or what is the alternate way to read this hiv e table using spark

hive table sample format:

  CREATE TABLE `hive``(                   |
|   `c_id` decimal(11,0),etc.........       
  ROW FORMAT SERDE                                   |
|   'org.apache.hadoop.hive.ql.io.orc.OrcSerde'      |
| WITH SERDEPROPERTIES (  
 STORED AS INPUTFORMAT                              |
|   'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'  |
| OUTPUTFORMAT                                       |
|   'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat' |
 LOCATION                                           |
|  path= 'hdfs://gjuyada/bbts/scl/raw' |
| TBLPROPERTIES (                                    |
|   'bucketing_version'='2',                         |
|   'spark.sql.create.version'='2.3.2.3.1.0.0-78',   |
|   'spark.sql.sources.provider'='orc',              |
|   'spark.sql.sources.schema.numParts'='1',         |
|   'spark.sql.sources.schema.part.0'='{"type":"struct","fields":
[{"name":"Czz_ID","type":"decimal(11,0)","nullable":true,"metadata":{}},
{"name":"DzzzC_CD","type":"string","nullable":true,"metadata":{}},
{"name":"C0000_S_N","type":"decimal(11,0)","nullable":true,"metadata":{}},
{"name":"P_ _NB","type":"decimal(11,0)","nullable":true,"metadata":{}},
{"name":"C_YYYY","type":"string","nullable":true,"metadata":{}},"type":"string","nullable":true,"metadata":{}},{"name":"Cv_ID","type":"string","nullable":true,"metadata":{}},
|   'transactional'='true',                          |
|   'transient_lastDdlTime'='1574817059')

share the hive table information that you are trying to read. — dassum, Feb 06 '20 at 17:22

score 6 · Accepted Answer · edited Oct 14 '21 at 14:19

6

The issue you are trying to reading Transactional table(transactional = true) into Spark.

Officially Spark not yet supported for Hive-ACID table, get a full dump/incremental dump of acid table to regular hive orc/parquet partitioned table then read the data using spark.

There is a Open Jira SPARK-15348 to add support for reading Hive ACID table.

If you run major compaction on Acid table(from hive) then spark able to read base_XXX directories only but not delta directories SPARK-16996 addressed in this jira.
There are some workaround to read acid tables using SPARK-LLAP as mentioned in this link.
I think starting from HDP-3.X HiveWareHouseConnector is able to support to read HiveAcid tables.
You can create an snapshot of the transactional table as non transactional and then read the data from the table.
```
 **`create table <non_trans> stored as orc as select * from <transactional_table>`**
```

UPDATE:

1.Create an external hive table:

 CREATE external TABLE `<ext_tab_name>`(  
       <col_name>       <data_type>....etc
           )
    stored as orc
    location '<path>';

2.Then overwrite to the above external table with existing transactional table data.

 insert overwrite table <ext_tab_name> select * from <transactional_tab_name>;

edited Oct 14 '21 at 14:19

Indent

4,675
1
19
35

answered Feb 06 '20 at 20:49

notNull

30,258
4
35
50

could you give some info related to creating snapshot of non-transnational table from transnational ,do i need to mention transnational=false while creating hive table from transnational ? – Db8 Feb 07 '20 at 13:51
1

@user12763124, no need i think, if ur using `HDP-3` then make sure the snapshot table is not transactional enabled!, **(or)** you can `create an external table` with `same schema as transactional table` then **insert overwrite to external table** by *selecting data from transactional table*!! – notNull Feb 07 '20 at 17:16
**create table stored as orc as select * from ** is showing bucket out of range -1 error or **create an external table with same schema as transactional table** is giving same bucket out of range -1 error – Db8 Feb 07 '20 at 19:19
yes it is transactional =true on the hive table that was intially created using spark – Db8 Feb 07 '20 at 20:48
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/207439/discussion-between-shu-and-user12763124). – notNull Feb 07 '20 at 21:36

score 2 · Answer 2 · answered May 25 '21 at 04:38

You need to create table with TBLPROPERTIES ( 'transactional'='false' )

CREATE TABLE your_table(
  `col` string, 
  `col2` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
TBLPROPERTIES ( 
  'transactional'='false' 
)

score 1 · Answer 3 · answered Jan 11 '22 at 20:52

Check out https://github.com/qubole/spark-acid - Hive ACID Data Source for Apache Spark, which will allow you to read Hive ACID/transaction tables.

If you are using Cloudera, check out Hive Warehouse Connector (HWC), which is based on the qubole project:

HWC main page
If you can run the Spark app as a super user and you don't need authorization, use the faster Spark Direct Reader mode: blog post

spark throws error when reading hive transaction table

3 Answers3

Linked