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.