Creating Hive table for Boolean field

Viewed 98

I've sample parquet data in S3 with schema as below.

>>> spark.read.parquet("s3://mybucket/someprefix/data/").printSchema()
root                                                                            
 |-- cust_id: string (nullable = true)
 |-- cust_first_name: string (nullable = true)
 |-- cust_last_name: string (nullable = true)
 |-- member_since: string (nullable = true)
 |-- refered_by: string (nullable = true)
 |-- cust_addr: string (nullable = true)
 |-- package: string (nullable = true)
 |-- exact_location: array (nullable = true)
 |    |-- element: boolean (containsNull = true)
 |-- position: array (nullable = true)
 |    |-- element: long (containsNull = true)
 |-- rcvd_dt: integer (nullable = true)

The sample data is here -


+-------------+----------------+---------------------+------------------------+-------------+-------------------+---------+--------------+--------+-------------+
|cust_id      |cust_first_name |cust_last_name       |member_since            |refered_by   |cust_addr          |package  |exact_location|position|received_date|
+-------------+----------------+---------------------+------------------------+-------------+-------------------+---------+--------------+--------+-------------+
|88351        |Kevin           |Knowles              |2021-12-15T00:52:24.177Z|73008        |kKnowles@yahoo.com |global   |[true]        |[5]     |20220114     |
+-------------+----------------+---------------------+------------------------+-------------+-------------------+---------+--------------+--------+-------------+ 
 

Now I tried to create a hive table on top of this data and tried to query in hive but hitting an error as below.

create external table cust_serv_dev(
    cust_id  string,
    cust_first_name string,
    cust_last_name string,
    member_since string,
    refered_by string,
    cust_addr string,
    package string,
    exact_location array<string>, 
    position array<string>)
partitioned by (
    rcvd_dt  string)
stored as parquet 
location "s3://mybucket/someprefix/data/"; 

msck repair table cust_serv_dev ; 

select * from cust_serv_dev; 
OK
Failed with exception java.io.IOException:org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.ClassCastException: org.apache.hadoop.io.ArrayWritable cannot be cast to org.apache.hadoop.io.BooleanWritable

I'm trying to see if there's any easy way to create a table on top of this data. array<string> boolean and string datatypes are not working for the fields exact_location and position.

Please help if there's a simple way to create a hive table. This hive table will be accessed through Pyspark and databricks too. Thanks in advance.

0 Answers
Related