• Create
    • Ask a question
    • Create an article
    • Topics
    • Questions
    • Articles
    • Users
    • Badges
  • Sign in
  • Home /
avatar image
0

CREATE EXTERNAL TABLE fails with Parquet source containing DECIMAL type

parquetdecimalsql ddl
Question by stuarto · Jun 14, 2017 at 11:36 PM ·

I am trying to create an EXTERNAL TABLE from Parquet using SQL DDL as follows:

CREATE EXTERNAL TABLE foo (
  a  int,
  b  int,
  c  decimal(5,2))
STORED AS PARQUET
LOCATION 's3a://...:...@<bucket>/<path>' 
TBLPROPERTIES("parquet.compress"="SNAPPY")

and i get the error:

AnalysisException: org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.UnsupportedOperationException: Parquet does not support decimal. See HIVE-6384;


However, I can create a table from the same s3 source (parquet) using the Data UI tab in Databricks and get no problems, along with a Decimal column. The describe foo reports:

a int
b int
c decimal(5,2)

How can I create the same result using a SQL script? I have several tables and don't want to use the UI.

Add comment · Show 2
Comment
10 |600 characters needed characters left characters exceeded
▼
  • Viewable by all users
  • Viewable by moderators
  • Viewable by moderators and the original poster
  • Advanced visibility
Viewable by all users
avatar image stuarto · Jun 16, 2017 at 06:57 PM 0
Share

I believe the problem is that my cluster config shows the variable

spark.sql.hive.metastore.version 0.13.0

And that version doesn't support the richer Parquet column types. I am using the Databricks-provided metastore associated with the account. However when I launch a cluster with the version set to 1.2.1, all of my SQL requests fail with the message "Cancelled".

Is there a proven way to update the Databricks-provisioned metastore beyond 0.13.0 ??

avatar image dataForCivicGood · Nov 03, 2017 at 04:47 PM 0
Share

Hello,

I'm experiencing a similar issue.

What is the exact syntax for setting the hive metastore properties in pyspark?

I tried using the commands below, but I still get the '

AnalysisException: org.apache.hadoop.hive.ql.metadata.HiveException' error.

spark.sql("SET spark.sql.hive.metastore.version = 1.2.1")
spark.sql("SET spark.sql.hive.metastore.jars = builtin")

Thanks.

1 Answer

Sort

  • Votes
  • Created
  • Oldest
avatar image
0

Answer by stuarto · Jun 16, 2017 at 07:06 PM

Solved the issue. I needed to change 2 items from the default configuration of my cluster:

spark.sql.hive.metastore.version 1.2.1

spark.sql.hive.metastore.jars builtin

After that, the cluster was able to digest Parquet data containing decimal types.

Comment
Add comment · Share
10 |600 characters needed characters left characters exceeded
▼
  • Viewable by all users
  • Viewable by moderators
  • Viewable by moderators and the original poster
  • Advanced visibility
Viewable by all users

Your answer

Hint: You can notify a user about this post by typing @username

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this Question

12 People are following this question.

avatar image avatar image avatar image avatar image avatar image avatar image avatar image avatar image avatar image avatar image avatar image avatar image

Related Questions

Parquet & Snappy Conversion Recommendations 1 Answer

DataFrame append to Parquet file issues 2 Answers

Repartition and store in Parquet file 3 Answers

SparkR with StructType 0 Answers

nullable seemingly ignored when reading parquet 0 Answers

  • Product
    • Databricks Cloud
    • FAQ
  • Spark
    • About Spark
    • Developer Resources
    • Community + Events
  • Services
    • Certification
    • Spark Support
    • Spark Training
  • Company
    • About Us
    • Team
    • News
    • Contact
  • Careers
  • Blog

Databricks Inc.
160 Spear Street, 13th Floor
San Francisco, CA 94105

info@databricks.com
1-866-330-0121

  • Twitter
  • LinkedIn
  • Facebook
  • Facebook

© Databricks 2015. All rights reserved. Apache Spark and the Apache Spark Logo are trademarks of the Apache Software Foundation.

  • Anonymous
  • Sign in
  • Create
  • Ask a question
  • Create an article
  • Explore
  • Topics
  • Questions
  • Articles
  • Users
  • Badges