Pyspark - Looking to apply SQL queries to pyspark dataframes

user_13136602 0
Viewed 90

Disclaimer: I'm very new to pyspark and this question might not be appropriate.

I've seen the following code online:

# Get the id, age where age = 22 in SQL
spark.sql("select id, age from swimmers where age = 22").show()

Now, I've tried to pivot using pyspark with the following code:

complete_dataset.createOrReplaceTempView("df")
temp = spark.sql("SELECT core_id from df")

This is the error I'm getting:

'java.lang.RuntimeException: java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient;'

I figured this would be straightforward but I can't seem to find the solution. Is this posible to do in pyspark?

NOTE: I am on an EMR Cluster using a Pyspark notebook.

amazon-emr pyspark
1 Answers

In pyspark you can read MySQL table (assuming that you are using MySQL) and create dataframe.

    jdbc_url = 'jdbc:mysql://{}:{}@{}/{}?zeroDateTimeBehavior=CONVERT_TO_NULL'.format(
    'usrname',
    'password',
    'host',
    'db',
)

    table_df = sql_ctx.read.jdbc(url=jdbc_url, table='table_name').select("column_name1", "column_name2")

Where table_df is the dataframe. The you can do required operations on dataframe like filter etc.

table_df.filter(table_df.column1 == 'abc').show()
user_23234830
Related