Create Hive Table on partionned by year month day parquet

Question

I create a dataframe like this:

val df = Seq(
  (1,27,"bob",2020,9,3),
  (1,27,"jack",2020,9,3),
  (3,31,"tom",2020,9,4)
).toDF("id","age","nom","year","month","day")

I get the following dataframe

+---+---+----+----+-----+---+
|id |age|nom |year|month|day|
+---+---+----+----+-----+---+
|1  |27 |bob |2020|9    |3  |
|1  |27 |jack|2020|9    |3  |
|3  |31 |tom |2020|9    |4  |
+---+---+----+----+-----+---+

then I write df on hdfs with partitionBy usig year, month day;

df.write
  .mode(SaveMode.Append)
  .partitionBy("year", "month", "day")
  .parquet(s"$outputPath/test_hive")

I get data on following hdfs paths:

/outputPath/test_hive/year=2020/month=9/day=3
/outputPath/test_hive/year=2020/month=9/day=4

I wonder how can I create an external hive table at location outputPath/test_hive which could take into account subdirectories year, month and day.

I tried following create table but it does not works:

CREATE EXTERNAL TABLE test1(id int, age int, nom string, year int, month int, day int) STORED AS PARQUET LOCATION 'outputPath/test_hive'

+-----------+------------+------------+--+
| test1.id  | test1.age  | test1.nom  |
+-----------+------------+------------+--+
| 1         | 27         | bob        |
| 1         | 27         | jack       |
| 3         | 31         | tom        |
+-----------+------------+------------+--+

and

CREATE EXTERNAL TABLE test2(id int, age int, nom string) PARTITIONED BY(year INT, month int , day INT) STORED AS PARQUET LOCATION 'outputPath/test_hive'

+-----------+------------+------------+-------------+--------------+------------+--+
| test2.id  | test2.age  | test2.nom  | test2.year  | test2.month  | test2.day  |
+-----------+------------+------------+-------------+--------------+------------+--+
+-----------+------------+------------+-------------+--------------+------------+--+

and

CREATE EXTERNAL TABLE test3(id int, age int, nom string) STORED AS PARQUET LOCATION 'outputPath/test_hive' PARTITIONED BY(year INT, month int , day INT);

Error while compiling statement: FAILED: ParseException line 1:138 missing EOF at 'PARTITIONED' near ''/outputPath/test_hive'' (state=42000,code=40000)

what about change stored as and partitioned by order of the second one? — Lamanus
@Lamanus I tried to change order as you mentionned with the third one I just edited but i get an Error: Error while compiling statement: FAILED: ParseException line 1:138 missing EOF at 'PARTITIONED' near ''/outputPath/test_hive'' (state=42000,code=40000) — a.moussa
@Lamanus I get no error but i don't have expected data. I add to the question above results from hive — a.moussa

Shu Shu · Accepted Answer · 2020-09-05T15:43:29

Do msck repair table or add partitions to the table.

Example:

From Hive:

hive> msck repair table test3

--or

hive> ALTER TABLE test3 ADD PARTITION (year='2020', month='9',day='3') location '/outputPath/test_hive/year=2020/month=9/day=3';

From spark:

spark.sql("ALTER TABLE test3 ADD PARTITION (year='2020', month='9',day='3') location '/outputPath/test_hive/year=2020/month=9/day=3'")

//or

spark.sql("msck repair table test3")

Create Hive Table on partionned by year month day parquet

1 Answers