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.executor.instances": 10,
        "spark.dla.job.log.oss.uri": "oss://test/spark-logs",
        "spark.executor.resourceSpec": "small"
    }
}

1. The "sqls" keyword allows you to submit SQL jobs without the need to submit JAR packages. This makes the operation easier and is suitable for developers who are familiar with SQL. The parameter value is an array, which allows you to execute multiple SQL statements in a job. In this example, multiple SQL statements are separated by commas (,).

2. You can submit Spark SQL jobs in the DLA console or by using APIs. The method of submitting Spark SQL jobs by using APIs is the same as the method of submitting other types of Spark jobs.

3. You can also submit Spark SQL jobs by using the Spark-SQL toolkit. For more information, see

   Spark-SQL tool.

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.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.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 execute the DROP statement to delete a table or a partition of a table in the Spark engine, the files are not deleted from the OSS bucket where the table is stored.

• The LOCATION parameter that you specified when you create a table must be the subfolder of the LOCATION parameter where the database is stored.

• The PARTITION LOCATION parameter that you specified when you add a partition for the table must be the subfolder of the LOCATION parameter where the table is stored.

• The RENAME PARTITION statement does not change the directory structure of OSS to which the partition belongs.

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

• Do not create or perform operations on a database named DEFAULT in the Spark engine.

• Before you perform operations on the table, you must execute the USE DatabaseName statement to switch to the required database. You can also explicitly specify the database to which a table belongs, such as SELELCT * FROM db1.table1.

• The ALTER statements can only be executed to modify the comment of a database. They 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. Note that the column cannot be a partition key column.

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