spark-sql-function

PySpark DataFrame - Convert JSON Column to Row using json_tuple

PySpark SQL functions json_tuple can be used to convert DataFrame JSON string columns to tuples (new rows in the DataFrame). Syntax of this function looks like the following: `` pyspark.sql.functions.json_tuple(col, *fields) ` The first parameter is the JSON string column name in the DataFrame and the second is the filed name list to extract. If you need to extract complex JSON documents like JSON arrays, you can follow this article - PySpark: Convert JSON String Column to Array of Object (StructType) in DataFrame. Output ` StructType([StructField('id', LongType(), True), StructField('c0', StringType(), True), StructField('c1', StringType(), True), StructField('c2', StringType(), True)]) +---+---+------+----------+ | id| c0| c1| c2| +---+---+------+----------+ | 1| 1|10.201|2021-01-01| | 2| 2|20.201|2022-01-01| +---+---+------+----------+ ``

PySpark DataFrame - Extract JSON Value using get_json_object Function

PySpark SQL functions getjsonobject can be used to extract JSON values from a JSON string column in Spark DataFrame. This is equivalent as using Spark SQL directly: Spark SQL - Extract Value from JSON String. Syntax of this function looks like the following: `` pyspark.sql.functions.getjsonobject(col, path) ` The first parameter is the JSON string column name in the DataFrame and the second is the JSON path. This code snippet shows you how to extract JSON values using JSON path. If you need to extract complex JSON documents like JSON arrays, you can follow this article - PySpark: Convert JSON String Column to Array of Object (StructType) in DataFrame. Output ` StructType([StructField('id', LongType(), True), StructField('jsoncol', StringType(), True), StructField('ATTRINT0', StringType(), True), StructField('ATTRDATE_1', StringType(), True)]) +---+--------------------+----------+-----------+ | id| jsoncol|ATTRINT0|ATTRDATE_1| +---+--------------------+----------+-----------+ | 1|[{"Attr_INT":1, "...| 1| 2022-01-01| +---+--------------------+----------+-----------+ ``

Replace Values via regexp_replace Function in PySpark DataFrame

PySpark SQL APIs provides regexp_replace built-in function to replace string values that match with the specified regular expression. It takes three parameters: the input column of the DataFrame, regular expression and the replacement for matches. `` pyspark.sql.functions.regexp_replace(str, pattern, replacement) ` Output The following is the output from this code snippet: ` +--------------+-------+----------------+ | strcol|intcol|strcolreplaced| +--------------+-------+----------------+ |Hello Kontext!| 100| Hello kontext!| |Hello Context!| 100| Hello kontext!| +--------------+-------+----------------+ `` All uppercase 'K' or 'C' are replaced with lowercase 'k'.

Spark SQL - window Function

Spark SQL has built-in function window to bucketize rows into one or more time windows given a timestamp specifying column. The syntax of the function looks like the following: window(timeColumn: ColumnOrName, windowDuration: str, slideDuration: Optional[str] = None, startTime: Optional[str] = None) This function is available from Spark 2.0.0. slideDuration must be less than or equal to windowDuration. \*These SQL statements can be directly used in PySpark DataFrame APIs too via spark.sql function. This code snippet prints out the following outputs: Query 1: `` 2022-08-01 12:01:00 {"start":2022-08-01 12:00:00,"end":2022-08-01 12:30:00} 2022-08-01 12:15:00 {"start":2022-08-01 12:00:00,"end":2022-08-01 12:30:00} 2022-08-01 12:31:01 {"start":2022-08-01 12:30:00,"end":2022-08-01 13:00:00} ` The first two rows are in the same window [00:00, 00:30). Query 2: ` 2022-08-01 12:01:00 {"start":2022-08-01 12:00:00,"end":2022-08-01 12:30:00}2022-08-01 12:01:00 {"start":2022-08-01 11:45:00,"end":2022-08-01 12:15:00}2022-08-01 12:15:00 {"start":2022-08-01 12:15:00,"end":2022-08-01 12:45:00}2022-08-01 12:15:00 {"start":2022-08-01 12:00:00,"end":2022-08-01 12:30:00}2022-08-01 12:31:01 {"start":2022-08-01 12:30:00,"end":2022-08-01 13:00:00}2022-08-01 12:31:01 {"start":2022-08-01 12:15:00,"end":2022-08-01 12:45:00} ``

Spark SQL - session_window Function

Spark SQL has built-in function session_window to create a window column based on a timestamp column and gap duration. The syntax of the function looks like the following: session\_window(timeColumn: ColumnOrName, gapDuration: [pyspark.sql.column.Column, str]) This function is available from Spark 3.2.0. \*These SQL statements can be directly used in PySpark DataFrame APIs too via spark.sql function. This code snippet prints out the following output: `` 2022-08-01 12:01:00 {"start":2022-08-01 12:01:00,"end":2022-08-01 12:31:00} 2022-08-01 12:15:00 {"start":2022-08-01 12:15:00,"end":2022-08-01 12:45:00} 2022-08-01 12:31:01 {"start":2022-08-01 12:31:01,"end":2022-08-01 13:01:01} ``

Spark SQL - Left and Right Padding (lpad and rpad) Functions

Spark SQL - Check if String Contains a String

Spark SQL - isnull and isnotnull Functions

Spark SQL - Concatenate w/o Separator (concat_ws and concat)

Spark SQL - Create Map from Arrays via map_from_arrays Function

Spark Hash Functions Introduction - MD5 and SHA

Spark SQL - Get Next Monday, Tuesday, Wednesday, Thursday, etc.

Spark SQL - Make Date, Timestamp and Intervals

Spark SQL - Get Current Timezone

Spark SQL - Date and Timestamp Truncate Functions

Spark SQL - Extract Day, Month, Year and other Part from Date or Timestamp

Spark SQL - Add Day, Month and Year to Date

Spark SQL - Return JSON Array Length (json_array_length)

Spark SQL - Return JSON Object Keys (json_object_keys)

Spark SQL - Conversion between UTC and Timestamp with Time Zone

Spark SQL - Date/Timestamp Conversation from/to UNIX Date/Timestamp

Spark SQL - Convert Date/Timestamp to String via date_format Function

Spark SQL - Convert Delimited String to Map using str_to_map Function

Spark SQL - element_at Function

Spark SQL - flatten Function

Spark SQL - PERCENT_RANK Window Function

Spark SQL - Date Difference in Seconds, Minutes, Hours

Spark "ROW_ID"

Spark SQL - PIVOT Clause

Spark SQL - Calculate Covariance

Spark SQL - Standard Deviation Calculation

Spark SQL - FIRST_VALUE or LAST_VALUE

Spark SQL - Array Functions

Spark SQL - Map Functions

Spark SQL - Convert Object to JSON String

Spark SQL - Extract Value from JSON String

Spark SQL - Convert JSON String to Map

Spark SQL - Convert String to Timestamp

Spark SQL - UNIX timestamp functions

Spark SQL - Date and Timestamp Function

Spark SQL - LEAD Window Function

Spark SQL - LAG Window Function

Spark SQL - NTILE Window Function

Spark SQL - DENSE_RANK Window Function

Spark SQL - RANK Window Function

Spark SQL - ROW_NUMBER Window Functions