1

I'm making some automatic processes to create tables on Cloudera Hive.

For that I am using the show create table statement that me give (for example) the following ddl:

CREATE TABLE clsd_core.factual_player (   player_name STRING,   number_goals INT ) PARTITIONED BY (   player_name STRING ) WITH SERDEPROPERTIES ('serialization.format'='1') STORED AS PARQUET LOCATION 'hdfs://nameservice1/factual_player'

What I need is to run the ddl on a different place to create a table with the same name.

However, when I run that code I return the following error:

Error while compiling statement: FAILED: ParseException line 1:123 missing EOF at 'WITH' near ')'

And I remove manually this part "WITH SERDEPROPERTIES ('serialization.format'='1')" it was able to create the table with success.

Is there a better function to retrieves the tables ddls without the SERDE information?

leftjoin
  • 36,950
  • 8
  • 57
  • 116
Pedro Alves
  • 1,004
  • 1
  • 21
  • 47

1 Answers1

0

First issue in your DDL is that partitioned column should not be listed in columns spec, only in the partitioned by. Partition is the folder with name partition_column=value and this column is not stored in the table files, only in the partition directory. If you want partition column to be in the data files, it should be named differently.

Second issue is that SERDEPROPERTIES is a part of SERDE specification, If you do not specify SERDE, it should be no SERDEPROPERTIES. See this manual: StorageFormat andSerDe

Fixed DDL:

 CREATE TABLE factual_player (number_goals INT) 
 PARTITIONED BY (player_name STRING) 
 STORED AS PARQUET
 LOCATION 'hdfs://nameservice1/factual_player';

STORED AS PARQUET already implies SERDE, INPUTFORMAT and OUPPUTFORMAT.

If you want to specify SERDE with it's properties, use this syntax:

CREATE TABLE factual_player(number_goals int)
PARTITIONED BY (player_name string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
WITH SERDEPROPERTIES ('serialization.format'='1') --I believe you really do not need this
STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION 'hdfs://nameservice1/factual_player'
leftjoin
  • 36,950
  • 8
  • 57
  • 116
  • My problem is: I don't need to specify SERDE, but everytime that I make a show create table the DDL comes with SERDE properties – Pedro Alves Feb 26 '20 at 11:04
  • @PedroAlves Yes it does. STORED AS PARQUET is much simpler. The reason SHOW CREATE TABLE displays all the details maybe in case you need them. Just use STORED AS PARQUET when creating table. Unfortunately it seems there is not function which displays short DDL – leftjoin Feb 26 '20 at 11:07