Apache Spark

https://spark.apache.org/docs/latest/
Admin
Apache Spark is a unified analytics engine for large-scale data processing with high-level APIs in...
Tokens:14,152,518
Snippets:129,011
Trust Score:10
Update:1 week ago
Show doc for...
Tokens:
Raw
### Setup for Index.identical examples

Source: https://spark.apache.org/docs/latest/api/python/reference/pyspark.pandas/api/pyspark.pandas.Index.identical.html

Imports option_context and creates an Index and a MultiIndex for the examples.

```python
>>> from pyspark.pandas.config import option_context
>>> idx = ps.Index(['a', 'b', 'c'])
>>> midx = ps.MultiIndex.from_tuples([('a', 'x'), ('b', 'y'), ('c', 'z')])
```

--------------------------------

### Setup for equals examples

Source: https://spark.apache.org/docs/latest/api/python/reference/pyspark.pandas/api/pyspark.pandas.CategoricalIndex.equals.html

Initializes an Index and a MultiIndex with names for the equality examples.

```python
>>> from pyspark.pandas.config import option_context
>>> idx = ps.Index(['a', 'b', 'c'])
>>> idx.name = "name"
>>> midx = ps.MultiIndex.from_tuples([('a', 'x'), ('b', 'y'), ('c', 'z')])
>>> midx.names = ("nameA", "nameB")
```

--------------------------------

### WHERE clause examples with table setup and queries

Source: https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-where.html

A set of examples demonstrating the WHERE clause with various expressions: comparison, logical operators, IS NULL, function, BETWEEN, scalar subquery, and correlated subquery. The examples use a person table created and populated at the start.

```sql
CREATE TABLE person (id INT, name STRING, age INT);
INSERT INTO person VALUES
    (100, 'John', 30),
    (200, 'Mary', NULL),
    (300, 'Mike', 80),
    (400, 'Dan',  50);

```

```sql
-- Comparison operator in `WHERE` clause.
SELECT * FROM person WHERE id > 200 ORDER BY id;
+---+----+---+
| id|name|age|
+---+----+---+
|300|Mike| 80|
|400| Dan| 50|
+---+----+---+

```

```sql
-- Comparison and logical operators in `WHERE` clause.
SELECT * FROM person WHERE id = 200 OR id = 300 ORDER BY id;
+---+----+----+
| id|name| age|
+---+----+----+
|200|Mary|null|
|300|Mike|  80|
+---+----+----+

```

```sql
-- IS NULL expression in `WHERE` clause.
SELECT * FROM person WHERE id > 300 OR age IS NULL ORDER BY id;
+---+----+----+
| id|name| age|
+---+----+----+
|200|Mary|null|
|400| Dan|  50|
+---+----+----+

```

```sql
-- Function expression in `WHERE` clause.
SELECT * FROM person WHERE length(name) > 3 ORDER BY id;
+---+----+----+
| id|name| age|
+---+----+----+
|100|John|  30|
|200|Mary|null|
|300|Mike|  80|
+---+----+----+

```

```sql
-- `BETWEEN` expression in `WHERE` clause.
SELECT * FROM person WHERE id BETWEEN 200 AND 300 ORDER BY id;
+---+----+----+
| id|name| age|
+---+----+----+
|200|Mary|null|
|300|Mike|  80|
+---+----+----+

```

```sql
-- Scalar Subquery in `WHERE` clause.
SELECT * FROM person WHERE age > (SELECT avg(age) FROM person);
+---+----+---+
| id|name|age|
+---+----+---+
|300|Mike| 80|
+---+----+---+

```

```sql
-- Correlated Subquery in `WHERE` clause.
SELECT * FROM person AS parent
    WHERE EXISTS (
        SELECT 1 FROM person AS child
        WHERE parent.id = child.id AND child.age IS NULL
    );
+---+----+----+
|id |name|age |
+---+----+----+
|200|Mary|null|
+---+----+----+

```

--------------------------------

### DataFrame.xs example with MultiIndex setup

Source: https://spark.apache.org/docs/latest/api/python/_modules/pyspark/pandas/frame.html

Creates a DataFrame with a MultiIndex from columns 'class', 'animal', and 'locomotion', then displays it. This setup is used for subsequent xs examples.

```python
>>> d = {'num_legs': [4, 4, 2, 2],
...      'num_wings': [0, 0, 2, 2],
...      'class': ['mammal', 'mammal', 'mammal', 'bird'],
...      'animal': ['cat', 'dog', 'bat', 'penguin'],
...      'locomotion': ['walks', 'walks', 'flies', 'walks']}
>>> df = ps.DataFrame(data=d)
>>> df = df.set_index(['class', 'animal', 'locomotion'])
>>> df  # doctest: +NORMALIZE_WHITESPACE
                           num_legs  num_wings
class  animal  locomotion
mammal cat     walks              4          0
       dog     walks              4          0
       bat     flies              2          2
bird   penguin walks              2          2
```

--------------------------------

### startTime property example

Source: https://spark.apache.org/docs/latest/api/python/_modules/pyspark/core/context.html

Get the epoch time when the SparkContext was started.

```python
>>> _ = sc.startTime
```

--------------------------------

### SHOW PARTITIONS examples

Source: https://spark.apache.org/docs/latest/sql-ref-syntax-aux-show-partitions.html

A series of examples demonstrating SHOW PARTITIONS. It starts by creating a partitioned table and inserting rows, then shows how to list all partitions, list partitions for a qualified table, and use full or partial partition specs to filter results.

```sql
-- create a partitioned table and insert a few rows.
USE salesdb;
CREATE TABLE customer(id INT, name STRING) PARTITIONED BY (state STRING, city STRING);
INSERT INTO customer PARTITION (state = 'CA', city = 'Fremont') VALUES (100, 'John');
INSERT INTO customer PARTITION (state = 'CA', city = 'San Jose') VALUES (200, 'Marry');
INSERT INTO customer PARTITION (state = 'AZ', city = 'Peoria') VALUES (300, 'Daniel');

-- Lists all partitions for table `customer`
SHOW PARTITIONS customer;
+----------------------+
|             partition|
+----------------------+
|  state=AZ/city=Peoria|
| state=CA/city=Fremont|
|state=CA/city=San Jose|
+----------------------+

-- Lists all partitions for the qualified table `customer`
SHOW PARTITIONS salesdb.customer;
+----------------------+
|             partition|
+----------------------+
|  state=AZ/city=Peoria|
| state=CA/city=Fremont|
|state=CA/city=San Jose|
+----------------------+

-- Specify a full partition spec to list specific partition
SHOW PARTITIONS customer PARTITION (state = 'CA', city = 'Fremont');
+---------------------+
|            partition|
+---------------------+
|state=CA/city=Fremont|
+---------------------+

-- Specify a partial partition spec to list the specific partitions
SHOW PARTITIONS customer PARTITION (state = 'CA');
+----------------------+
|             partition|
+----------------------+
| state=CA/city=Fremont|
|state=CA/city=San Jose|
+----------------------+

-- Specify a partial spec to list specific partition
SHOW PARTITIONS customer PARTITION (city =  'San Jose');
+----------------------+
|             partition|
+----------------------+
|state=CA/city=San Jose|
+----------------------+
```

--------------------------------

### Example: Get Spark version

Source: https://spark.apache.org/docs/latest/api/R/reference/sparkR.version.html

Example usage of sparkR.version() within an if(FALSE) block, indicating it is not run automatically. It starts a Spark session and then retrieves the version.

```R
if (FALSE) {
sparkR.session()
version <- sparkR.version()
}
```

--------------------------------

### SHOW DATABASES Examples

Source: https://spark.apache.org/docs/latest/sql-ref-syntax-aux-show-databases.html

Examples showing how to create databases, list all databases, filter by a pattern, and use the SCHEMAS keyword. The output tables are displayed as they appear in the source.

```sql
-- Create database. Assumes a database named `default` already exists in
-- the system. 
CREATE DATABASE payroll_db;
CREATE DATABASE payments_db;

-- Lists all the databases. 
SHOW DATABASES;
+------------+
|databaseName|
+------------+
|     default|
| payments_db|
|  payroll_db|
+------------+
  
-- Lists databases with name starting with string pattern `pay`
SHOW DATABASES LIKE 'pay*';
+------------+
|databaseName|
+------------+
| payments_db|
|  payroll_db|
+------------+
  
-- Lists all databases. Keywords SCHEMAS and DATABASES are interchangeable. 
SHOW SCHEMAS;
+------------+
|databaseName|
+------------+
|     default|
| payments_db|
|  payroll_db|
+------------+
```

--------------------------------

### Example: Get SparkUI URL

Source: https://spark.apache.org/docs/latest/api/R/reference/sparkR.uiWebUrl.html

Example usage of sparkR.uiWebUrl. It is wrapped in if (FALSE) to prevent execution during documentation checks. It starts a Spark session and retrieves the SparkUI URL.

```r
if (FALSE) {
sparkR.session()
url <- sparkR.uiWebUrl()
}
```

--------------------------------

### Example: count rows in a SparkDataFrame

Source: https://spark.apache.org/docs/latest/api/R/reference/nrow.html

Example wrapped in if (FALSE) to prevent execution. It starts a SparkR session, reads a JSON file, and calls count(df) to get the number of rows.

```r
if (FALSE) {
sparkR.session()
path <- "path/to/file.json"
df <- read.json(path)
count(df)
}
```

--------------------------------

### CREATE DATABASE examples

Source: https://spark.apache.org/docs/latest/sql-ref-syntax-ddl-create-database.html

Examples of creating a database with and without IF NOT EXISTS, and with COMMENT, LOCATION, and DBPROPERTIES, followed by a DESCRIBE DATABASE EXTENDED query to verify the properties.

```sql
-- Create database `customer_db`. This throws exception if database with name customer_db
-- already exists.
CREATE DATABASE customer_db;

-- Create database `customer_db` only if database with same name doesn't exist.
CREATE DATABASE IF NOT EXISTS customer_db;

-- Create database `customer_db` only if database with same name doesn't exist with 
-- `Comments`,`Specific Location` and `Database properties`.
CREATE DATABASE IF NOT EXISTS customer_db COMMENT 'This is customer database' LOCATION '/user'
    WITH DBPROPERTIES (ID=001, Name='John');

-- Verify that properties are set.
DESCRIBE DATABASE EXTENDED customer_db;
+-------------------------+--------------------------+
|database_description_item|database_description_value|
+-------------------------+--------------------------+
|            Database Name|               customer_db|
|              Description| This is customer database|
|                 Location|     hdfs://hacluster/user|
|               Properties|   ((ID,001), (Name,John))|
+-------------------------+--------------------------+

```

--------------------------------

### Create table and DESCRIBE TABLE examples

Source: https://spark.apache.org/docs/latest/sql-ref-syntax-aux-describe-table.html

Shows creating a partitioned customer table, inserting a row, then running DESCRIBE TABLE on unqualified and qualified names, and DESCRIBE TABLE EXTENDED. Outputs show the returned metadata columns.

```sql
-- Creates a table `customer`. Assumes current database is `salesdb`.
CREATE TABLE customer(
        cust_id INT,
        state VARCHAR(20),
        name STRING COMMENT 'Short name'
    )
    USING parquet
    PARTITIONED BY (state);
    
INSERT INTO customer PARTITION (state = 'AR') VALUES (100, 'Mike');
    
-- Returns basic metadata information for unqualified table `customer`
DESCRIBE TABLE customer;
+-----------------------+---------+----------+
|               col_name|data_type|   comment|
+-----------------------+---------+----------+
|                cust_id|      int|      null|
|                   name|   string|Short name|
|                  state|   string|      null|
|# Partition Information|         |          |
|             # col_name|data_type|   comment|
|                  state|   string|      null|
+-----------------------+---------+----------+

-- Returns basic metadata information for qualified table `customer`
DESCRIBE TABLE salesdb.customer;
+-----------------------+---------+----------+
|               col_name|data_type|   comment|
+-----------------------+---------+----------+
|                cust_id|      int|      null|
|                   name|   string|Short name|
|                  state|   string|      null|
|# Partition Information|         |          |
|             # col_name|data_type|   comment|
|                  state|   string|      null|
+-----------------------+---------+----------+

-- Returns additional metadata such as parent database, owner, access time etc.
DESCRIBE TABLE EXTENDED customer;
+----------------------------+------------------------------+----------+
|                    col_name|                     data_type|   comment|
+----------------------------+------------------------------+----------+
|                     cust_id|                           int|      null|
|                        name|                        string|Short name|
|                       state|                        string|      null|
|     # Partition Information|                              |          |
|                  # col_name|                     data_type|   comment|
|                       state|                        string|      null|
|                            |                              |          |
|# Detailed Table Information|                              |          |
|                    Database|                       default|          |
|                       Table|                      customer|          |
|                       Owner|                 <TABLE OWNER>|          |
|                Created Time|  Tue Apr 07 22:56:34 JST 2020|          |
|                 Last Access|                       UNKNOWN|          |
|                  Created By|               <SPARK VERSION>|          |
|                        Type|                       MANAGED|          |
|                    Provider|                       parquet|          |
|                    Location|file:/tmp/salesdb.db/custom...|          |
|               Serde Library|org.apache.hadoop.hive.ql.i...|          |
|                 InputFormat|org.apache.hadoop.hive.ql.i...|          |
|                OutputFormat|org.apache.hadoop.hive.ql.i...|          |
|          Partition Provider|                       Catalog|          |
+----------------------------+------------------------------+----------+
```

--------------------------------

### SHOW TBLPROPERTIES examples

Source: https://spark.apache.org/docs/latest/sql-ref-syntax-aux-show-tblproperties.html

Examples demonstrating creating a table with properties, showing all properties, showing properties for a qualified table, and showing a specific property using unquoted and string literal keys.

```sql
-- create a table `customer` in database `salesdb`
USE salesdb;
CREATE TABLE customer(cust_code INT, name VARCHAR(100), cust_addr STRING)
    TBLPROPERTIES ('created.by.user' = 'John', 'created.date' = '01-01-2001');

-- show all the user specified properties for table `customer`
SHOW TBLPROPERTIES customer;
+---------------------+----------+
|                  key|     value|
+---------------------+----------+
|      created.by.user|      John|
|         created.date|01-01-2001|
|transient_lastDdlTime|1567554931|
+---------------------+----------+

-- show all the user specified properties for a qualified table `customer`
-- in database `salesdb`
SHOW TBLPROPERTIES salesdb.customer;
+---------------------+----------+
|                  key|     value|
+---------------------+----------+
|      created.by.user|      John|
|         created.date|01-01-2001|
|transient_lastDdlTime|1567554931|
+---------------------+----------+

-- show value for unquoted property key `created.by.user`
SHOW TBLPROPERTIES customer (created.by.user);
+-----+
|value|
+-----+
| John|
+-----+

-- show value for property `created.date`` specified as string literal
SHOW TBLPROPERTIES customer ('created.date');
+----------+
|     value|
+----------+
|01-01-2001|
+----------+

```

--------------------------------

### storageLevel example: persist and retrieve storage level

Source: https://spark.apache.org/docs/latest/api/R/reference/storageLevel.html

Example usage of storageLevel. It starts a SparkR session, reads a JSON file, persists the DataFrame with 'MEMORY_AND_DISK', and then calls storageLevel to get the storage level. The example is wrapped in if (FALSE) to prevent execution during package checks.

```R
if (FALSE) {
sparkR.session()
path <- "path/to/file.json"
df <- read.json(path)
persist(df, "MEMORY_AND_DISK")
storageLevel(df)
}
```

--------------------------------

### Import Row and Get SparkContext

Source: https://spark.apache.org/docs/latest/sql-getting-started.html

This snippet is the start of the Python example for inferring schema by reflection, showing how to import the Row class and obtain the SparkContext.

```python
from pyspark.sql import Row

sc = spark.sparkContext
```

--------------------------------

### Creating and Populating a Table for CASE Examples

Source: https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-case.html

Setup script to create and populate the 'person' table used in subsequent CASE clause examples.

```sql
CREATE TABLE person (id INT, name STRING, age INT);
INSERT INTO person VALUES
    (100, 'John', 30),
    (200, 'Mary', NULL),
    (300, 'Mike', 80),
    (400, 'Dan', 50);
```

--------------------------------

### pyspark.pandas.DataFrame.loc

Source: https://spark.apache.org/docs/latest/api/python/reference/pyspark.pandas/api/pyspark.pandas.DataFrame.loc.html

The .loc accessor is used for label-based indexing. The examples demonstrate getting values and slicing rows with integer labels, where both start and stop are inclusive.

```APIDOC
## pyspark.pandas.DataFrame.loc

### Description
Label-based indexing and slicing for a pandas-on-Spark DataFrame. Use integer labels to select rows and columns. Slices are inclusive of both start and stop labels.

### Usage
python
df.loc[row_indexer, column_indexer]


### Parameters
- **row_indexer** (slice, list, or scalar) - Required - Row labels to select. Can be a slice, list of labels, or a single label.
- **column_indexer** (slice, list, or scalar) - Optional - Column labels to select. Defaults to all columns if omitted.

### Examples

#### Getting values with integer labels
python
>>> df = ps.DataFrame([[1, 2], [4, 5], [7, 8]],
...                   index=[7, 8, 9],
...                   columns=['max_speed', 'shield'])
>>> df
   max_speed  shield
7          1       2
8          4       5
9          7       8


#### Slicing with integer labels (inclusive)
python
>>> df.loc[7:9]
   max_speed  shield
7          1       2
8          4       5
9          7       8


### Notes
- Both the start and stop labels are included in the slice.
- The index must have integer labels for the examples shown.
```

--------------------------------

### Create database and tables for ANALYZE TABLE examples

Source: https://spark.apache.org/docs/latest/sql-ref-syntax-aux-analyze-table.html

Sets up a sample database with teachers and partitioned students tables for the ANALYZE TABLE examples. These statements create the database, tables, and insert data used in the subsequent examples.

```sql
CREATE DATABASE school_db;
USE school_db;

CREATE TABLE teachers (name STRING, teacher_id INT);
INSERT INTO teachers VALUES ('Tom', 1), ('Jerry', 2);

CREATE TABLE students (name STRING, student_id INT) PARTITIONED BY (student_id);
INSERT INTO students VALUES ('Mark', 111111), ('John', 222222);

```

--------------------------------

### Create a DataFrame with columns x, y, z

Source: https://spark.apache.org/docs/latest/api/python/reference/pyspark.pandas/api/pyspark.pandas.DataFrame.get.html

Initializes a pandas-on-Spark DataFrame with three columns and an index containing duplicate values. This is the setup for the subsequent get examples.

```python
>>> df = ps.DataFrame({'x':range(3), 'y':['a','b','b'], 'z':['a','b','b']},
...                   columns=['x', 'y', 'z'], index=[10, 20, 20])
>>> df
    x  y  z
10  0  a  a
20  1  b  b
20  2  b  b
```

--------------------------------

### ADD FILE examples

Source: https://spark.apache.org/docs/latest/sql-ref-syntax-aux-resource-mgmt-add-file.html

Examples of adding files and directories to the resource list, including quoted paths and paths with spaces.

```sql
ADD FILE /tmp/test;
ADD FILE "/path/to/file/abc.txt";
ADD FILE '/another/test.txt';
ADD FILE "/path with space/abc.txt";
ADD FILE "/path/to/some/directory";
ADD FILES "/path with space/cde.txt" '/path with space/fgh.txt';
```

--------------------------------

### Create a DataFrame with columns x, y, z

Source: https://spark.apache.org/docs/latest/api/python/reference/pyspark.pandas/api/pyspark.pandas.Series.get.html

Creates a PySpark pandas DataFrame with columns 'x', 'y', 'z' and index [10, 20, 20]. This is the setup for the subsequent get examples.

```python
>>> df = ps.DataFrame({'x':range(3), 'y':['a','b','b'], 'z':['a','b','b']},
...                   columns=['x', 'y', 'z'], index=[10, 20, 20])
>>> df
    x  y  z
10  0  a  a
20  1  b  b
20  2  b  b

```

--------------------------------

### Get recent progress updates with recentProgress property

Source: https://spark.apache.org/docs/latest/api/python/_modules/pyspark/sql/streaming/query.html

Creates a rate stream, starts a memory query, and retrieves the most recent progress updates. The example shows the expected output as a list and then stops the query.

```python
>>> sdf = spark.readStream.format("rate").load()
>>> sq = sdf.writeStream.format('memory').queryName('this_query').start()

Get an array of the most recent query progress updates for this query

>>> sq.recentProgress
[...]

>>> sq.stop()
```

--------------------------------

### show_options() example

Source: https://spark.apache.org/docs/latest/api/python/_modules/pyspark/pandas/config.html

Shows how to call show_options() to display available options. The comment instructs to copy the output into the documentation.

```python
>>> from pyspark.pandas.config import show_options
>>> show_options()
```

--------------------------------

### Setup sample data for customers and orders

Source: https://spark.apache.org/docs/latest/api/python/_modules/pyspark/sql/dataframe.html

Creates sample DataFrames for customers and orders used in the EXISTS subquery examples. The data includes customer IDs, names, countries, and order details.

```python
>>> data_customers = [
...     (101, "Alice", "USA"), (102, "Bob", "Canada"), (103, "Charlie", "USA"),
...     (104, "David", "Australia")
... ]
>>> data_orders = [
...     (1, 101, "2023-01-15", 250), (2, 102, "2023-01-20", 300),
...     (3, 103, "2023-01-25", 400), (4, 101, "2023-02-05", 150)
... ]
>>> customers = spark.createDataFrame(
...     data_customers, ["customer_id", "customer_name", "country"])
>>> orders = spark.createDataFrame(
...     data_orders, ["order_id", "customer_id", "order_date", "total_amount"])
```

--------------------------------

### slice SQL examples

Source: https://spark.apache.org/docs/latest/api/sql/array-functions

Subsets array starting from index (1-based, negative starts from end). Two examples show positive and negative start. Since 2.4.0.

```sql
> SELECT slice(array(1, 2, 3, 4), 2, 2);
 [2,3]
> SELECT slice(array(1, 2, 3, 4), -2, 2);
 [3,4]
```

--------------------------------

### Get current status of streaming query with status property

Source: https://spark.apache.org/docs/latest/api/python/_modules/pyspark/sql/streaming/query.html

Creates a rate stream, starts a memory query, and retrieves the current status. The example shows the expected output format with placeholders and then stops the query.

```python
>>> sdf = spark.readStream.format("rate").load()
>>> sq = sdf.writeStream.format('memory').queryName('this_query').start()

Get the current status of the query

>>> sq.status
{'message': '...', 'isDataAvailable': ..., 'isTriggerActive': ...}

>>> sq.stop()
```

--------------------------------

### SHOW TABLES examples

Source: https://spark.apache.org/docs/latest/sql-ref-syntax-aux-show-tables.html

Examples of SHOW TABLES with different database specifications and LIKE patterns, each followed by its output table.

```sql
-- List all tables in default database
SHOW TABLES;
+--------+---------+-----------+
|database|tableName|isTemporary|
+--------+---------+-----------+
| default|      sam|      false|
| default|     sam1|      false|
| default|      suj|      false|
+--------+---------+-----------+

```

```sql
-- List all tables from userdb database 
SHOW TABLES FROM userdb;
+--------+---------+-----------+
|database|tableName|isTemporary|
+--------+---------+-----------+
|  userdb|    user1|      false|
|  userdb|    user2|      false|
+--------+---------+-----------+

```

```sql
-- List all tables in userdb database
SHOW TABLES IN userdb;
+--------+---------+-----------+
|database|tableName|isTemporary|
+--------+---------+-----------+
|  userdb|    user1|      false|
|  userdb|    user2|      false|
+--------+---------+-----------+

```

```sql
-- List all tables from default database matching the pattern `sam*`
SHOW TABLES FROM default LIKE 'sam*';
+--------+---------+-----------+
|database|tableName|isTemporary|
+--------+---------+-----------+
| default|      sam|      false|
| default|     sam1|      false|
+--------+---------+-----------+

```

```sql
-- List all tables matching the pattern `sam*|suj`
SHOW TABLES LIKE 'sam*|suj';
+--------+---------+-----------+
|database|tableName|isTemporary|
+--------+---------+-----------+
| default|      sam|      false|
| default|     sam1|      false|
| default|      suj|      false|
+--------+---------+-----------+

```

--------------------------------

### date_range examples: specifying start and periods

Source: https://spark.apache.org/docs/latest/api/python/_modules/pyspark/pandas/namespace.html

Example of using ps.date_range with start and periods parameters, generating a daily frequency DatetimeIndex.

```python
>>> ps.date_range(start='1/1/2018', periods=8)  # doctest: +SKIP
DatetimeIndex(['2018-01-01', '2018-01-02', '2018-01-03', '2018-01-04',
               '2018-01-05', '2018-01-06', '2018-01-07', '2018-01-08'],
              dtype='datetime64[ns]', freq=None)
```

--------------------------------

### SORT BY examples with REPARTITION hint

Source: https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-sortby.html

Creates a person table, inserts sample data, and runs four SORT BY queries with REPARTITION hint to show sorting within partitions. Includes outputs for each query.

```sql
CREATE TABLE person (zip_code INT, name STRING, age INT);
INSERT INTO person VALUES
    (94588, 'Zen Hui', 50),
    (94588, 'Dan Li', 18),
    (94588, 'Anil K', 27),
    (94588, 'John V', NULL),
    (94511, 'David K', 42),
    (94511, 'Aryan B.', 18),
    (94511, 'Lalit B.', NULL);

-- Use `REPARTITION` hint to partition the data by `zip_code` to
-- examine the `SORT BY` behavior. This is used in rest of the
-- examples.

-- Sort rows by `name` within each partition in ascending manner
SELECT /*+ REPARTITION(zip_code) */ name, age, zip_code FROM person SORT BY name;
+--------+----+--------+
|    name| age|zip_code|
+--------+----+--------+
|  Anil K|  27|   94588|
|  Dan Li|  18|   94588|
|  John V|null|   94588|
| Zen Hui|  50|   94588|
|Aryan B.|  18|   94511|
| David K|  42|   94511|
|Lalit B.|null|   94511|
+--------+----+--------+

-- Sort rows within each partition using column position.
SELECT /*+ REPARTITION(zip_code) */ name, age, zip_code FROM person SORT BY 1;
+--------+----+--------+
|    name| age|zip_code|
+--------+----+--------+
|  Anil K|  27|   94588|
|  Dan Li|  18|   94588|
|  John V|null|   94588|
| Zen Hui|  50|   94588|
|Aryan B.|  18|   94511|
| David K|  42|   94511|
|Lalit B.|null|   94511|
+--------+----+--------+

-- Sort rows within partition in ascending manner keeping null values to be last.
SELECT /*+ REPARTITION(zip_code) */ age, name, zip_code FROM person SORT BY age NULLS LAST;
+----+--------+--------+
| age|    name|zip_code|
+----+--------+--------+
|  18|  Dan Li|   94588|
|  27|  Anil K|   94588|
|  50| Zen Hui|   94588|
|null|  John V|   94588|
|  18|Aryan B.|   94511|
|  42| David K|   94511|
|null|Lalit B.|   94511|
+----+--------+--------+

-- Sort rows by age within each partition in descending manner, which defaults to NULL LAST.
SELECT /*+ REPARTITION(zip_code) */ age, name, zip_code FROM person SORT BY age DESC;
+----+--------+--------+
| age|    name|zip_code|
+----+--------+--------+
|  50| Zen Hui|   94588|
|  27|  Anil K|   94588|
|  18|  Dan Li|   94588|
|null|  John V|   94588|
|  42| David K|   94511|
|  18|Aryan B.|   94511|
|null|Lalit B.|   94511|
+----+--------+--------+

```

--------------------------------

### getTable examples: create table, get table, drop table

Source: https://spark.apache.org/docs/latest/api/python/_modules/pyspark/sql/catalog.html

Shows how to create a table, retrieve it with getTable, and drop it. The output shows the Table object with name, catalog, and namespace.

```python
>>> _ = spark.sql("DROP TABLE IF EXISTS tbl1")
>>> _ = spark.sql("CREATE TABLE tbl1 (name STRING, age INT) USING parquet")
>>> spark.catalog.getTable("tbl1")
Table(name='tbl1', catalog='spark_catalog', namespace=['default'], ...
```

```python
>>> _ = spark.sql("DROP TABLE tbl1")
```

--------------------------------

### date_range examples: specifying start and end

Source: https://spark.apache.org/docs/latest/api/python/_modules/pyspark/pandas/namespace.html

Example of using ps.date_range with start and end parameters, generating a daily frequency DatetimeIndex.

```python
>>> ps.date_range(start='1/1/2018', end='1/08/2018')  # doctest: +SKIP
DatetimeIndex(['2018-01-01', '2018-01-02', '2018-01-03', '2018-01-04',
               '2018-01-05', '2018-01-06', '2018-01-07', '2018-01-08'],
              dtype='datetime64[ns]', freq=None)
```

--------------------------------

### Partitioning hints examples

Source: https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-hints.html

Examples of partitioning hints: COALESCE, REPARTITION, REPARTITION_BY_RANGE, and REBALANCE. These hints tune performance and control the number of output files. When multiple partitioning hints are specified, the leftmost hint is picked by the optimizer.

```sql
SELECT /*+ COALESCE(3) */ * FROM t;

SELECT /*+ REPARTITION(3) */ * FROM t;

SELECT /*+ REPARTITION(c) */ * FROM t;

SELECT /*+ REPARTITION(3, c) */ * FROM t;

SELECT /*+ REPARTITION_BY_RANGE(c) */ * FROM t;

SELECT /*+ REPARTITION_BY_RANGE(3, c) */ * FROM t;

SELECT /*+ REBALANCE */ * FROM t;

SELECT /*+ REBALANCE(3) */ * FROM t;

SELECT /*+ REBALANCE(c) */ * FROM t;

SELECT /*+ REBALANCE(3, c) */ * FROM t;
```

--------------------------------

### Example: fit, predict, fitted, and save/load a bisecting k-means model

Source: https://spark.apache.org/docs/latest/api/R/reference/spark.bisectingKmeans.html

Complete example wrapped in if (FALSE) that starts a SparkR session, creates a SparkDataFrame from the Titanic dataset, fits a bisecting k-means model with k=4, prints summary, gets fitted centers, predicts on training data, saves the model, and reads it back.

```r
if (FALSE) {
sparkR.session()
t <- as.data.frame(Titanic)
df <- createDataFrame(t)
model <- spark.bisectingKmeans(df, Class ~ Survived, k = 4)
summary(model)

# get fitted result from a bisecting k-means model
fitted.model <- fitted(model, "centers")
showDF(fitted.model)

# fitted values on training data
fitted <- predict(model, df)
head(select(fitted, "Class", "prediction"))

# save fitted model to input path
path <- "path/to/model"
write.ml(model, path)

# can also read back the saved model and print
savedModel <- read.ml(path)
summary(savedModel)
}
```

--------------------------------

### ADD JAR examples

Source: https://spark.apache.org/docs/latest/sql-ref-syntax-aux-resource-mgmt-add-jar.html

Examples of ADD JAR with local paths, quoted paths, multiple jars, and Ivy URIs with transitive and exclude parameters.

```sql
ADD JAR /tmp/test.jar;
ADD JAR "/path/to/some.jar";
ADD JAR '/some/other.jar';
ADD JAR "/path with space/abc.jar";
ADD JARS "/path with space/def.jar" '/path with space/ghi.jar';
ADD JAR "ivy://group:module:version";
ADD JAR "ivy://group:module:version?transitive=false";
ADD JAR "ivy://group:module:version?transitive=true";
ADD JAR "ivy://group:module:version?exclude=group:module&transitive=true";

```

--------------------------------

### time_diff examples

Source: https://spark.apache.org/docs/latest/api/sql/datetime-functions

Gets the difference between two TIME expressions in the specified unit. Allowed units are HOUR, MINUTE, SECOND, MILLISECOND, and MICROSECOND. The result can be negative when the start time is later than the end time. Available since 4.1.0.

```sql
> SELECT time_diff('HOUR', TIME'20:30:29', TIME'21:30:28');
 0
```

```sql
> SELECT time_diff('HOUR', TIME'20:30:29', TIME'21:30:29');
 1
```

```sql
> SELECT time_diff('HOUR', TIME'20:30:29', TIME'12:00:00');
 -8
```

--------------------------------

### substr without length (defaults to 2147483647)

Source: https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.substr.html

Extracts substring from a given position to the end of the string when length is omitted. The example uses sf.substr(df.a, df.b) to get substring starting at position 5, resulting in 'k SQL'.

```python
>>> df.select("*", sf.substr(df.a, df.b)).show()
+---------+---+---+------------------------+
|        a|  b|  c|substr(a, b, 2147483647)|
+---------+---+---+------------------------+
|Spark SQL|  5|  1|                   k SQL|
+---------+---+---+------------------------+

```

--------------------------------

### Create and populate sample person table for LIMIT examples

Source: https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-limit.html

Creates a sample table and inserts six rows used by all LIMIT examples that follow. This setup must run before the SELECT queries in the same snippet group.

```sql
CREATE TABLE person (name STRING, age INT);
INSERT INTO person VALUES
    ('Zen Hui', 25),
    ('Anil B', 18),
    ('Shone S', 16),
    ('Mike A', 25),
    ('John A', 18),
    ('Jack N', 16);
```

--------------------------------

### date_range examples: specifying start, end, and periods

Source: https://spark.apache.org/docs/latest/api/python/_modules/pyspark/pandas/namespace.html

Example of using ps.date_range with start, end, and periods parameters, generating a linearly spaced DatetimeIndex.

```python
>>> ps.date_range(start='1/1/2018', end='1/08/2018', periods=8)  # doctest: +SKIP
DatetimeIndex(['2018-01-01', '2018-01-02', '2018-01-03', '2018-01-04',
               '2018-01-05', '2018-01-06', '2018-01-07', '2018-01-08'],
              dtype='datetime64[ns]', freq=None)
```

--------------------------------

### DROP VIEW Examples

Source: https://spark.apache.org/docs/latest/sql-ref-syntax-ddl-drop-view.html

Examples showing how to drop views with different qualifications, handle non-existent views with and without IF EXISTS, and drop temporary vs persistent views when shadowing occurs.

```sql
-- Assumes a view named `employeeView` exists.
DROP VIEW employeeView;

-- Assumes a view named `employeeView` exists in the `userdb` database
DROP VIEW userdb.employeeView;

-- Assumes a view named `employeeView` does not exist.
-- Throws exception
DROP VIEW employeeView;
Error: TABLE_OR_VIEW_NOT_FOUND

-- Assumes a view named `employeeView` does not exist,Try with IF EXISTS
-- this time it will not throw exception
DROP VIEW IF EXISTS employeeView;

-- A temporary view that shadows a persistent view with the same name.
-- An unqualified DROP VIEW drops the temporary view first; qualifying with `session`
-- always targets the temporary view explicitly.
CREATE VIEW default.recent_orders AS SELECT * FROM orders WHERE order_date > current_date - 7;
CREATE TEMPORARY VIEW recent_orders AS SELECT * FROM orders WHERE order_date = current_date;

DROP VIEW session.recent_orders;             -- drops the temporary view
DROP VIEW default.recent_orders;             -- drops the persistent view

```

--------------------------------

### LIST ARCHIVE examples

Source: https://spark.apache.org/docs/latest/sql-ref-syntax-aux-resource-mgmt-list-archive.html

Demonstrates adding archives with ADD ARCHIVE and listing them with LIST ARCHIVE. The first example lists all archives, showing file paths. The second example lists only the specified archive, ignoring non-existent files.

```sql
ADD ARCHIVE /tmp/test.zip;
ADD ARCHIVE /tmp/test_2.tar.gz;
LIST ARCHIVE;
-- output for LIST ARCHIVE
file:/tmp/test.zip
file:/tmp/test_2.tar.gz

```

```sql
LIST ARCHIVE /tmp/test.zip /some/random.tgz /another/random.tar;
-- output
file:/tmp/test.zip

```

--------------------------------

### RENAME partition example with SHOW PARTITIONS output

Source: https://spark.apache.org/docs/latest/sql-ref-syntax-ddl-alter-table.html

Example showing how to rename a partition using ALTER TABLE ... PARTITION ... RENAME TO PARTITION. Includes SHOW PARTITIONS output before and after the rename to verify the change.

```sql
-- RENAME partition

SHOW PARTITIONS StudentInfo;
+---------+
|partition|
+---------+
|   age=10|
|   age=11|
|   age=12|
+---------+

ALTER TABLE default.StudentInfo PARTITION (age='10') RENAME TO PARTITION (age='15');

-- After renaming Partition
SHOW PARTITIONS StudentInfo;
+---------+
|partition|
+---------+
|   age=11|
|   age=12|
|   age=15|
+---------+
```

--------------------------------

### overlay example

Source: https://spark.apache.org/docs/latest/api/python/_modules/pyspark/sql/functions/builtin.html

Overlays a portion of a string with a replacement string starting at a byte position. The example replaces part of 'SPARK_SQL' with 'CORE' starting at position 7.

```python
>>> from pyspark.sql import functions as sf
>>> df = spark.createDataFrame([("SPARK_SQL", "CORE")], ("x", "y"))
>>> df.select("*", sf.overlay("x", df.y, 7)).show()
+---------+----+--------------------+
|        x|   y|overlay(x, y, 7, -1)|
+---------+----+--------------------+
|SPARK_SQL|CORE|          SPARK_CORE|
+---------+----+--------------------+
```

--------------------------------

### EXPLAIN FORMATTED example

Source: https://spark.apache.org/docs/latest/sql-ref-syntax-qry-explain.html

Shows the EXPLAIN FORMATTED output, which provides a physical plan outline and node details.

```sql
-- Using Formatted
EXPLAIN FORMATTED select k, sum(v) from values (1, 2), (1, 3) t(k, v) group by k;
+----------------------------------------------------+
|                                                plan|
+----------------------------------------------------+
| == Physical Plan ==
 * HashAggregate (4)
 +- Exchange (3)
    +- * HashAggregate (2)
       +- * LocalTableScan (1)
   
   
 (1) LocalTableScan [codegen id : 1]
 Output: [k#19, v#20]
        
 (2) HashAggregate [codegen id : 1]
 Input: [k#19, v#20]
        
 (3) Exchange
 Input: [k#19, sum#24L]
        
 (4) HashAggregate [codegen id : 2]
 Input: [k#19, sum#24L]
|
+----------------------------------------------------+
```

--------------------------------

### gettingResultTime() - TaskInfo

Source: https://spark.apache.org/docs/latest/api/java/index-all.html

Gets the time when the task started remotely getting the result.

```APIDOC
## gettingResultTime() - TaskInfo

### Description
Gets the time when the task started remotely getting the result.

### Method
`gettingResultTime()`

### Class
`org.apache.spark.scheduler.TaskInfo`

### Returns
- **long** - The time in milliseconds.
```

--------------------------------

### Explain plans for a streaming query

Source: https://spark.apache.org/docs/latest/api/python/_modules/pyspark/sql/streaming/query.html

Shows how to create a streaming query, print the schema, and explain the physical and logical plans. The example includes the schema output and the plan output placeholders.

```python
>>> sdf = spark.readStream.format("rate").load()
>>> sdf.printSchema()
root
  |-- timestamp: timestamp (nullable = true)
  |-- value: long (nullable = true)

>>> sq = sdf.writeStream.format('memory').queryName('query_explain').start()
>>> sq.processAllAvailable() # Wait a bit to generate the runtime plans.

Explain the runtime plans

>>> sq.explain()
== Physical Plan ==
...
>>> sq.explain(True)
== Parsed Logical Plan ==
...
== Analyzed Logical Plan ==
...
== Optimized Logical Plan ==
...
== Physical Plan ==
...
>>> sq.stop()
```

--------------------------------

### lead window function examples

Source: https://spark.apache.org/docs/latest/api/python/_modules/pyspark/sql/functions/builtin.html

Shows how to use sf.lead to get the value from a subsequent row in a window partition. The examples create a DataFrame, define a window, and apply lead with different offsets and defaults, demonstrating the resulting output.

```python
>>> from pyspark.sql import functions as sf
>>> from pyspark.sql import Window
>>> df = spark.createDataFrame(
...     [("a", 1), ("a", 2), ("a", 3), ("b", 8), ("b", 2)], ["c1", "c2"])
>>> df.show()
+---+---+
| c1| c2|
+---+---+
|  a|  1|
|  a|  2|
|  a|  3|
|  b|  8|
|  b|  2|
+---+---+
```

```python
>>> w = Window.partitionBy("c1").orderBy("c2")
>>> df.withColumn("next_value", sf.lead("c2").over(w)).show()
+---+---+----------+
| c1| c2|next_value|
+---+---+----------+
|  a|  1|         2|
|  a|  2|         3|
|  a|  3|      NULL|
|  b|  2|         8|
|  b|  8|      NULL|
+---+---+----------+
```

```python
>>> df.withColumn("next_value", sf.lead("c2", 1, 0).over(w)).show()
+---+---+----------+
| c1| c2|next_value|
+---+---+----------+
|  a|  1|         2|
|  a|  2|         3|
|  a|  3|         0|
|  b|  2|         8|
|  b|  8|         0|
+---+---+----------+
```

```python
>>> df.withColumn("next_value", sf.lead("c2", 2, -1).over(w)).show()
+---+---+----------+
| c1| c2|next_value|
+---+---+----------+
|  a|  1|         3|
|  a|  2|        -1|
|  a|  3|        -1|
|  b|  2|        -1|
|  b|  8|        -1|
+---+---+----------+
```

--------------------------------

### range() examples with end, start, numPartitions, and table alias

Source: https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-tvf.html

Shows four range() calls: with only an end, with start and end, with numPartitions, and with a table alias. Each example includes its output table.

```sql
-- range call with end
SELECT * FROM range(6 + cos(3));
+---+
| id|
+---+
|  0|
|  1|
|  2|
|  3|
|  4|
+---+

-- range call with start and end
SELECT * FROM range(5, 10);
+---+
| id|
+---+
|  5|
|  6|
|  7|
|  8|
|  9|
+---+

-- range call with numPartitions
SELECT * FROM range(0, 10, 2, 200);
+---+
| id|
+---+
|  0|
|  2|
|  4|
|  6|
|  8|
+---+

-- range call with a table alias
SELECT * FROM range(5, 8) AS test;
+---+
| id|
+---+
|  5|
|  6|
|  7|
+---+
```
Tokens:
Raw