from pyspark.sql.functions import col
# File location and type
file_location = “/FileStore/tables/sales_data_part1.csv”
file_type = “csv”
# CSV options
infer_schema = “false”
first_row_is_header = “true”
delimiter = “,”
# The applied options are for CSV files. For other file types, these will be ignored.
df = spark.read.format(file_type) \
.option(“inferSchema”, infer_schema) \
.option(“header”, first_row_is_header) \
.option(“sep”, delimiter) \
.load(file_location)
display(df)
# Renaming column names methods.
#method-1 to rename column name
# Rename single column
df1=df.withColumnRenamed(“InvoiceNo”, “InvNo”)
# Rename multiple columns
df2=df.withColumnRenamed(“StockCode”, “StkCode”).withColumnRenamed(“Quantity”, “Qty”).withColumnRenamed(“InvoiceDate”, “InvDayte”)
df.display()
df1.display()
df2.display()
# Method-2 for renaming columns. THis will actully reduce the number of columns from select-list.
df3 = df.selectExpr(“InvoiceNo as Inv_no”, “StockCode as stk_code”, “Description as Desc”)
df.display()
df3.display()
# Method-3 for renaming columns. THis will actully reduce the number of columns from select-list.
# # Remember: To use “col” function you need to import it using following
# from pyspark.sql.functions import col
df4 = df.select(col(“InvoiceNo”).alias(“inv”), )
df4.display()
# Create a view or table
temp_table_name = “sales_data_part1_csv”
df.createOrReplaceTempView(temp_table_name)
%sql
/* Query the created temp table in a SQL cell */
select * from sales_data_part1_csv
# With this registered as a temp view, it will only be available to this particular notebook. If you’d like other users to be able to query this table, you can also create a table from the DataFrame.
# Once saved, this table will persist across cluster restarts as well as allow various users across different notebooks to query this data.
# To do so, choose your table name and uncomment the bottom line.
permanent_table_name = “t_sales_data_part1_csv”
df.write.format(“parquet”).saveAsTable(permanent_table_name)
1.This
Notebook will be generated automatically when you load a CSV file in “DATA” section.
2.Note: Hyphen is not allowed in Table name
so replace all hyphens with underscores or other characters.
3.Parquet format is compressed Text format
and occupies much less space than CSV format. 2GB ASCII to 200MB Parquet.
4.Infer_schema=false shows all columns will come as
string data type. If Infer_schema=true then notebook will identify all datatypes and present in the table.