Spark SQL

{
    "sqls": [
        "select * from `1k_tables`.`table0` limit 100",
        "insert into `1k_tables`.`table0` values(1, 'test')"
    ],
    "name": "sql test",
    "jars": [
        "oss://test/hive-serde-3.1.2.jar"
    ],
    "conf": {
        "spark.dla.connectors": "oss",
        "spark.driver.resourceSpec": "small",
        "spark.sql.hive.metastore.version": "dla",
        "spark.sql.catalogImplementation": "hive",
        "spark.executor.instances": 10,
        "spark.dla.job.log.oss.uri": "oss://test/spark-logs",
        "spark.executor.resourceSpec": "small"
    }
}

Syntax of the sqls parameter

You can call SparkSession.sql to execute SQL statements by using one of the following methods:

• Specify SQL statements in the sqls parameter. After you submit a job, DLA extracts the statements from the sqls parameter and provides these statements to the template class of SQL statements. Then, the template class calls SparkSession.sql to execute these statements.

  Before you specify SQL statements in the sqls parameter, make sure that the characters in the statements comply with the JSON syntax. This is because the SQL statements are included in a JSON character string. Therefore, you must escape the following symbols before you enter the statements in the sqls parameter:

  Symbol	Symbol after escaping
  Double quotation marks (")	\"
  Line feed	\n
  Carriage return	\r
  Tab character	\t
  Backslash (\) (followed by unescaped characters such as d and w)	\\\\

  Note

  If you want to write the REGEX '\d+' regular expression in SQL statements, enter REGEX '\\\\d+' in the sqls parameter. This is because backslashes (\) in the JSON character string need to be escaped once. The regular expression that is submitted to SparkSession.sql is REGEX '\\d+'. Then, SparkSession.sql escapes the backslashes (\) in the REGEX '\\d+' regular expression. The final regular expression obtained by the serverless Spark engine is REGEX '\d+'.

  Example:

  // The source SQL file.
  select * from t where 
  col  regex  "\d+"
  
  // The SQL file after escaping.
  {
    "sqls":["select * from t where \n col regex \"\\\\d+\""],
    "conf":{}
  }

• Specify the OSS directory in the sqls parameter. We recommend that you use this method. The OSS directory stores an SQL file. The file contains the SQL statements that you want to execute. The SQL statements in the SQL file are separated by semicolons (;). The template class of SQL statements downloads SQL statements in the directory and submits these statements to SparkSession.sql. Then, SparkSession.sql executes these statements.

  SQL statements are directly stored in OSS and can be downloaded by the template class of SQL statements. Data transmission in the JSON format is not required. Therefore, you do not need to perform special processing on double quotation marks ("), line feeds, carriage returns, or tab characters. However, regular expressions must comply with the specifications of Spark SQL statements.

• Example:

  // The source SQL file.
  select * from t where 
  col  regex  "\d+"
  
  // The SQL file after escaping.
  select * from t where 
  col  regex  "\\d+"
  
  {
    "sqls":["oss://path/to/your.sql"],
    "conf":{}
  }

from pyspark.sql import SparkSession

if __name__ == "__main__":
    # init pyspark context
    spark = SparkSession \
        .builder \
        .appName("Python SQL Test") \
        .getOrCreate()
    
    # create a database
    spark.sql(
            "create database if not exists dlatest comment 'c' location 'oss://test/warehouse/' WITH DBPROPERTIES(k1='v1', k2='v2')")
    # create table
    spark.sql(
            "create table dlatest.tp(col1 INT)  PARTITIONED BY (p1 STRING, p2 STRING) location 'oss://test/warehouse/tp' STORED AS parquet TBLPROPERTIES ('parquet.compress'='SNAPPY')")
    # show structure
    print(spark.sql("show create table tp").collect()[0])
    
    # insert data
    spark.sql("INSERT into tp partition(p1='a',p2='a') values(1)")
    
    # show data
    spark.sql("select * from tp").show()

{
    "name": "DLA SQL Test",
    "file": "oss://path/to/example.py"
    "conf": {
        "spark.driver.resourceSpec": "small",
        "spark.sql.hive.metastore.version": "dla",
        "spark.sql.catalogImplementation": "hive",
        "spark.dla.connectors": "oss",
        "spark.executor.instances": 1,
        "spark.dla.job.log.oss.uri": "oss://path/to/spark-logs",
        "spark.executor.resourceSpec": "small"
    }
}

{
    "name": "DLA Meta Test",
    "sqls": ["SELECT * FROM HiveDB.JsonTable"],
    "jars": [
        "oss://test/hive-serde-3.1.2.jar"
    ],
    "conf": {
        "spark.driver.resourceSpec": "small",
        "spark.sql.hive.metastore.version": "dla",
        "spark.sql.catalogImplementation": "hive",
        "spark.dla.connectors": "oss",
        "spark.executor.instances": 1,
        "spark.dla.job.log.oss.uri": "oss://test/spark-logs",
        "spark.executor.resourceSpec": "small"
    }
}

CREATE DATABASE db1 LOCATION 'oss://test/db1/'

CREATE TABLE table1(col1 INT) LOCATION 'oss://test/db1/table1/'

• When you use Spark SQL to drop a DLA table or partition of a DLA table, the OSS table that is mapped to the DLA table is not deleted.

• When you create a table, LOCATION specified for the table must be a subfolder in the directory specified by LOCATION of the database.

• When you add a partition to a table, LOCATION specified for the partition must be a subfolder in the directory specified by LOCATION of the table.

• When you execute the RENAME PARTITION statement, the OSS directory structure does not change.

2. Spark SQL supports only external tables that are stored in OSS.

• You cannot create or perform operations on a database named DEFAULT in Spark SQL.

• Before you execute SQL statements in Spark SQL, you must execute the USE DatabaseName statement to switch to the destination database. You can also explicitly specify the database to which a table belongs in a SELECT statement, such as SELELCT * FROM db1.table1.

• The ALTER statements can only be executed to modify the comment of a database. The statements cannot be executed to modify the location or properties of a database.

• The ALTER statements can only be executed to modify the columns or properties of a table. For example, you can add a column or modify properties. In this case, the columns cannot be partition key columns.

CREATE TEMPORARY FUNCTION IF NOT EXISTS func1 as
'com.aliyun.dla.SimpleUDFExample' USING JAR '/path/to/jar1',
JAR '/path/to/jar2'

DROP DATABASE IF EXISTS db1