Convert a spark dataframe to a hive partitioned create table in pyspark using last two columns as partitions

user_2717470 0
Viewed 863

I have a dataframe in Pyspark(2.3) from which i need to generate a partitioned create table statement to run through spark.sql() to make it hive compatible .

Sample Dataframe:
 final.printSchema()
root
 |-- name: string (nullable = true)
 |-- age: string (nullable = true)
 |-- value: long (nullable = true)
 |-- date: string (nullable = true)
 |-- subid: string( nullable=true)

The script should read the dataframe and create the below table and consider the last two columns as the partitioned columns.

`create table schema.final( name string ,age string  ,value long ) 
     partitioned by (date string , subid string) stored as parquet;`

Any help with the above pyspark solution will be really great

pyspark-sql apache-spark-sql python-2.7
1 Answers

Here one approach by iterating through schema and generating the Hive SQL:

from pyspark.sql.types import StructType, StructField, StringType, LongType

schema = StructType([
  StructField('name', StringType()),
  StructField('age', StringType()),
  StructField('value', LongType()),
  StructField('date', StringType()),
  StructField('subid', StringType())
])

hiveCols = ""
hivePartitionCols = ""
for idx, c in enumerate(schema):
  # populate hive schema
  if(idx < len(schema[:-2])):
    hiveCols += "{0} {1}".format(c.name, c.dataType.simpleString())

    if(idx < len(schema[:-2]) - 1):
      hiveCols += ","


  # populate hive partition
  if(idx >= len(schema) - 2):
    hivePartitionCols += "{0} {1}".format(c.name, c.dataType.simpleString())

    if(idx < len(schema) - 1):
      hivePartitionCols += ","

hiveCreateSql = "create table schema.final({0}) partitioned by ({1}) stored as parquet".format(hiveCols, hivePartitionCols)
# create table schema.final(name string,age string,value bigint) partitioned by (date string,subid string) stored as parquet

spark.sql(hiveCreateSql)
user_7503760
Related