Quick Reference: Snowpark Java APIs for SQL Commands

This topic provides a quick reference of some of the Snowpark APIs that correspond to SQL commands.

(Note that this is not a complete list of the APIs that correspond to SQL commands.)

Performing Queries

Selecting Columns

To select specific columns, use select.

Example of a SQL Statement

Example of Snowpark Code

 
SELECT id, name FROM sample_product_data;
Copy
 
DataFrame df = session.table("sample_product_data");
DataFrame dfSelectedCols = df.select(Functions.col("id"), Functions.col("name"));
dfSelectedCols.show();
Copy

Renaming Columns

To rename a column, use as or alias.

Example of a SQL Statement

Example of Snowpark Code

 
SELECT id AS item_id FROM sample_product_data;
Copy
 
DataFrame df = session.table("sample_product_data");
DataFrame dfRenamedCol = df.select(Functions.col("id").as("item_id"));
dfRenamedCol.show();
Copy
 
DataFrame df = session.table("sample_product_data");
DataFrame dfRenamedCol = df.select(Functions.col("id").alias("item_id"));
dfRenamedCol.show();
Copy

Filtering Data

To filter data, use filter or where.

Example of a SQL Statement

Example of Snowpark Code

 
SELECT * FROM sample_product_data WHERE id = 1;
Copy
 
DataFrame df = session.table("sample_product_data");
DataFrame dfFilteredRows = df.filter(Functions.col("id").equal_to(Functions.lit(1)));
dfFilteredRows.show();
Copy
 
DataFrame df = session.table("sample_product_data");
DataFrame dfFilteredRows = df.where(Functions.col("id").equal_to(Functions.lit(1)));
dfFilteredRows.show();
Copy

Sorting Data

To sort data, use sort.

Example of a SQL Statement

Example of Snowpark Code

 
SELECT * FROM sample_product_data ORDER BY category_id;
Copy
 
DataFrame df = session.table("sample_product_data");
DataFrame dfSorted = df.sort(Functions.col("category_id"));
dfSorted.show();
Copy

Limiting the Number of Rows Returned

To limit the number of rows returned, use limit. See Limiting the Number of Rows in a DataFrame.

Example of a SQL Statement

Example of Snowpark Code

 
SELECT * FROM sample_product_data
  ORDER BY category_id LIMIT 2;
Copy
 
DataFrame df = session.table("sample_product_data");
DataFrame dfSorted = df.sort(Functions.col("category_id")).limit(2);
Row[] arrayRows = dfSorted.collect();
Copy

Performing Joins

To perform a join, use join or naturalJoin. See Joining DataFrames.

Example of a SQL Statement

Example of Snowpark Code

 
SELECT * FROM sample_a
  INNER JOIN sample_b
  on sample_a.id_a = sample_b.id_a;
Copy
 
DataFrame dfLhs = session.table("sample_a");
DataFrame dfRhs = session.table("sample_b");
DataFrame dfJoined =
  dfLhs.join(dfRhs, dfLhs.col("id_a").equal_to(dfRhs.col("id_a")));
dfJoined.show();
Copy
 
SELECT * FROM sample_a NATURAL JOIN sample_b;
Copy
 
DataFrame dfLhs = session.table("sample_a");
DataFrame dfRhs = session.table("sample_b");
DataFrame dfJoined = dfLhs.naturalJoin(dfRhs);
dfJoined.show();
Copy

Querying Semi-Structured Data

To traverse semi-structured data, use subField(“<field_name>”) and subField(<index>). See Working with Semi-Structured Data.

Example of a SQL Statement

Example of Snowpark Code

 
SELECT src:salesperson.name FROM car_sales;
Copy
 
DataFrame df = session.table("car_sales");
DataFrame dfJsonField =
  df.select(Functions.col("src").subField("salesperson").subField("name"));
dfJsonField.show();
Copy

Grouping and Aggregating Data

To group data, use groupBy. This returns a RelationalGroupedDataFrame object, which you can use to perform the aggregations.

Example of a SQL Statement

Example of Snowpark Code

 
SELECT category_id, count(*)
  FROM sample_product_data GROUP BY category_id;
Copy
 
DataFrame df = session.table("sample_product_data");
DataFrame dfCountPerCategory = df.groupBy(Functions.col("category_id")).count();
dfCountPerCategory.show();
Copy

Calling Window Functions

To call a window function, use the Window object methods to build a WindowSpec object, which in turn you can use for windowing functions (similar to using ‘<function> OVER … PARTITION BY … ORDER BY’).

Example of a SQL Statement

Example of Snowpark Code

 
SELECT category_id, id, SUM(amount) OVER
  (PARTITION BY category_id ORDER BY product_date)
  FROM sample_product_data ORDER BY product_date;
Copy
 
WindowSpec window = Window.partitionBy(
  Functions.col("category_id")).orderBy(Functions.col("product_date"));
DataFrame df = session.table("sample_product_data");
DataFrame dfCumulativePrices = df.select(
  Functions.col("category_id"), Functions.col("product_date"),
  Functions.sum(Functions.col("amount")).over(window)).sort(Functions.col("product_date"));
dfCumulativePrices.show();
Copy

Updating, Deleting, and Merging Rows

To update, delete, and merge rows in a table, use Updatable. See Updating, Deleting, and Merging Rows in a Table.

Example of a SQL Statement

Example of Snowpark Code

 
UPDATE sample_product_data
  SET serial_number = 'xyz' WHERE id = 12;
Copy
 
import java.util.HashMap;
import java.util.Map;
...

Map<Column, Column> assignments = new HashMap<>();
assignments.put(Functions.col("serial_number"), Functions.lit("xyz"));
Updatable updatableDf = session.table("sample_product_data");
UpdateResult updateResult =
  updatableDf.update(
    assignments,
    Functions.col("id").equal_to(Functions.lit(12)));
System.out.println("Number of rows updated: " + updateResult.getRowsUpdated());
Copy
 
DELETE FROM sample_product_data
  WHERE category_id = 50;
Copy
 
Updatable updatableDf = session.table("sample_product_data");
DeleteResult deleteResult =
  updatableDf.delete(updatableDf.col("category_id").equal_to(Functions.lit(50)));
System.out.println("Number of rows deleted: " + deleteResult.getRowsDeleted());
Copy
 
MERGE  INTO target_table USING source_table
  ON target_table.id = source_table.id
  WHEN MATCHED THEN
    UPDATE SET target_table.description =
      source_table.description;
Copy
 
import java.util.HashMap;
import java.util.Map;

Map<String, Column> assignments = new HashMap<>();
assignments.put("description", source.col("description"));
MergeResult mergeResult =
   target.merge(source, target.col("id").equal_to(source.col("id")))
  .whenMatched.updateColumn(assignments)
  .collect();
Copy

Working with Stages

For more information on working with stages, see Working With Files in a Stage.

Uploading and Downloading Files from a Stage

To upload and download files from a stage, use FileOperation. See Uploading and Downloading Files in a Stage.

Example of a SQL Statement

Example of Snowpark Code

 
PUT file:///tmp/*.csv @myStage OVERWRITE = TRUE;
Copy
 
import java.util.HashMap;
import java.util.Map;
...
Map<String, String> putOptions = new HashMap<>();
putOptions.put("OVERWRITE", "TRUE");
PutResult[] putResults = session.file().put(
  "file:///tmp/*.csv", "@myStage", putOptions);
for (PutResult result : putResults) {
  System.out.println(result.getSourceFileName() + ": " + result.getStatus());
}
Copy
 
GET @myStage file:///tmp PATTERN = '.*.csv.gz';
Copy
 
import java.util.HashMap;
import java.util.Map;
...
Map<String, String> getOptions = new HashMap<>();
getOptions.put("PATTERN", "'.*.csv.gz'");
GetResult[] getResults = session.file().get(
 "@myStage", "file:///tmp", getOptions);
for (GetResult result : getResults) {
  System.out.println(result.getFileName() + ": " + result.getStatus());
}
Copy

Reading Data from Files in a Stage

To read data from files in a stage, use DataFrameReader to create a DataFrame for the data. See Setting Up a DataFrame for Files in a Stage.

Example of a SQL Statement

Example of Snowpark Code

 
CREATE FILE FORMAT snowpark_temp_format TYPE = JSON;
SELECT "$1"[0]['salesperson']['name'] FROM (
  SELECT $1::VARIANT AS "$1" FROM @mystage/car_sales.json(
    FILE_FORMAT => 'snowpark_temp_format')) LIMIT 10;
DROP FILE FORMAT snowpark_temp_format;
Copy
 
DataFrame df = session.read().json(
  "@mystage/car_sales.json").select(
    Functions.col("$1").subField(0).subField("salesperson").subField("name"));
df.show();
Copy

Copying Data from Files in a Stage to a Table

To copy data from files in a stage to a table, use DataFrameReader to create a CopyableDataFrame for the data, and use the copyInto method to copy the data to the table. See Copying Data from Files into a Table.

Example of a SQL Statement

Example of Snowpark Code

 
COPY INTO new_car_sales
  FROM @mystage/car_sales.json
  FILE_FORMAT = (TYPE = JSON);
Copy
 
CopyableDataFrame dfCopyableDf = session.read().json("@mystage/car_sales.json");
dfCopyableDf.copyInto("new_car_sales");
Copy

Saving a DataFrame to Files on a Stage

To save a DataFrame to files on a stage, use the DataFrameWriter method named after the format of the files that you want to use. See Saving a DataFrame to Files on a Stage.

Example of a SQL Statement

Example of Snowpark Code

 
COPY INTO @mystage/saved_data.json
  FROM (  SELECT  *  FROM (car_sales) )
  FILE_FORMAT = ( TYPE = JSON COMPRESSION = 'none' )
  OVERWRITE = TRUE
  DETAILED_OUTPUT = TRUE
Copy
 
DataFrame df = session.table("car_sales");
WriteFileResult writeFileResult = df.write().mode(
  SaveMode.Overwrite).option(
  "DETAILED_OUTPUT", "TRUE").option(
  "compression", "none").json(
  "@mystage/saved_data.json");
Copy

Creating and Calling User-Defined Functions (UDFs)

To create an anonymous UDF, use Functions.udf.

To create a temporary or permanent UDF that you can call by name, use UDFRegistration.registerTemporary or UDFRegistration.registerPermanent.

To call a permanent UDF by name, use Functions.callUDF.

For details, see Creating User-Defined Functions (UDFs) for DataFrames in Java and Calling Scalar User-Defined Functions (UDFs).

Example of a SQL Statement

Example of Snowpark Code

 
CREATE FUNCTION <temp_function_name>
  RETURNS INT
  LANGUAGE JAVA
  ...
  AS
  ...;

SELECT ...,
  <temp_function_name>(quantity) AS doublenum
  FROM sample_product_data;
Copy
 
UserDefinedFunction doubleUdf =
  Functions.udf(
    (Integer x) -> x + x,
    DataTypes.IntegerType,
    DataTypes.IntegerType);
DataFrame df = session.table("sample_product_data");
DataFrame dfWithDoubleNum =
  df.withColumn("doubleNum",
    doubleUdf.apply(Functions.col("quantity")));
dfWithDoubleNum.show();
Copy
 
CREATE FUNCTION <temp_function_name>
  RETURNS INT
  LANGUAGE JAVA
  ...
  AS
  ...;

SELECT ...,
  <temp_function_name>(quantity) AS doublenum
  FROM sample_product_data;
Copy
 
UserDefinedFunction doubleUdf =
  session
    .udf()
    .registerTemporary(
      "doubleUdf",
      (Integer x) -> x + x,
      DataTypes.IntegerType,
      DataTypes.IntegerType);
DataFrame df = session.table("sample_product_data");
DataFrame dfWithDoubleNum =
  df.withColumn("doubleNum",
    Functions.callUDF("doubleUdf", Functions.col("quantity")));
dfWithDoubleNum.show();
Copy
 
CREATE FUNCTION doubleUdf(arg1 INT)
  RETURNS INT
  LANGUAGE JAVA
  ...
  AS
  ...;

SELECT ...,
  doubleUdf(quantity) AS doublenum
  FROM sample_product_data;
Copy
 
UserDefinedFunction doubleUdf =
  session
    .udf()
    .registerPermanent(
      "doubleUdf",
      (Integer x) -> x + x,
      DataTypes.IntegerType,
      DataTypes.IntegerType,
      "mystage");
DataFrame df = session.table("sample_product_data");
DataFrame dfWithDoubleNum =
  df.withColumn("doubleNum",
    Functions.callUDF("doubleUdf", Functions.col("quantity")));
dfWithDoubleNum.show();
Copy
Language: English