डॉट नोटेशन और SQL

Python में Spark SQL परिचय

Mark Plutowski

Data Scientist

हमारी टेबल में 3 कॉलम हैं

df.columns

['train_id', 'station', 'time']

df.show(5)

+--------+-------------+-----+
|train_id|      station| time|
+--------+-------------+-----+
|     324|San Francisco|7:59 |
|     324|  22nd Street|8:03 |
|     324|     Millbrae|8:16 |
|     324|    Hillsdale|8:24 |
|     324| Redwood City|8:31 |
+--------+-------------+-----+

हमें केवल 2 चाहिए

df.select('train_id','station')
  .show(5)

+--------+-------------+
|train_id|      station|
+--------+-------------+
|     324|San Francisco|
|     324|  22nd Street|
|     324|     Millbrae|
|     324|    Hillsdale|
|     324| Redwood City|
+--------+-------------+

2 कॉलम चुनने के 3 तरीके

df.select('train_id', 'station')
df.select(df.train_id, df.station)
from pyspark.sql.functions import col
df.select(col('train_id'), col('station'))

कॉलम का नाम बदलने के 2 तरीके

df.select('train_id','station')
  .withColumnRenamed('train_id','train')
  .show(5)

+-----+-------------+
|train|      station|
+-----+-------------+
|  324|San Francisco|
|  324|  22nd Street|
|  324|     Millbrae|
|  324|    Hillsdale|
|  324| Redwood City|
+-----+-------------+

df.select(col('train_id').alias('train'), 'station')

ऐसा न करें!

df.select('train_id', df.station, col('time'))

डॉट नोटेशन से SQL क्वेरी

spark.sql('SELECT train_id AS train, station FROM schedule LIMIT 5')
     .show()

+-----+-------------+
|train|      station|
+-----+-------------+
|  324|San Francisco|
|  324|  22nd Street|
|  324|     Millbrae|
|  324|    Hillsdale|
|  324| Redwood City|
+-----+-------------+

df.select(col('train_id').alias('train'), 'station')
  .limit(5)
  .show()

विंडो फंक्शन SQL

query = """
SELECT *, 
ROW_NUMBER() OVER(PARTITION BY train_id ORDER BY time) AS id 
FROM schedule
"""
spark.sql(query)
     .show(11)

विंडो फंक्शन SQL

+--------+-------------+-----+---+
|train_id|      station| time| id|
+--------+-------------+-----+---+
|     217|       Gilroy|6:06 |  1|
|     217|   San Martin|6:15 |  2|
|     217|  Morgan Hill|6:21 |  3|
|     217| Blossom Hill|6:36 |  4|
|     217|      Capitol|6:42 |  5|
|     217|       Tamien|6:50 |  6|
|     217|     San Jose|6:59 |  7|
|     324|San Francisco|7:59 |  1|
|     324|  22nd Street|8:03 |  2|
|     324|     Millbrae|8:16 |  3|
|     324|    Hillsdale|8:24 |  4|
+--------+-------------+-----+---+

डॉट नोटेशन से विंडो फंक्शन

from pyspark.sql import Window, 
from pyspark.sql.functions import row_number
df.withColumn("id", row_number()
                    .over(
                           Window.partitionBy('train_id')
                                 .orderBy('time')
                         )
  )

SQL में ROW_NUMBER : pyspark.sql.functions.row_number
OVER क्लॉज़ के अंदर वाला भाग : pyspark.sql.Window
PARTITION BY : pyspark.sql.Window.partitionBy
ORDER BY : pyspark.sql.Window.orderBy

WindowSpec का उपयोग

Spark SQL का over फंक्शन SQL की OVER क्लॉज़ से मेल खाता है.
क्लास pyspark.sql.window.Window OVER क्लॉज़ के अंदरूनी भाग को दर्शाती है.

window = Window.partitionBy('train_id').orderBy('time')
dfx = df.withColumn('next', lead('time',1).over(window))

ऊपर, type(window) है pyspark.sql.window.WindowSpec

अभ्यास करते हैं

Python में Spark SQL परिचय