• Home
  • LLMs
  • Docker
  • Kubernetes
  • Java
  • Maven
  • About
Big Data | Spark SQL CLI: spark-sql
  1. spark-sql command line options
  2. Start spark-sql
  3. spark-shell commands

  1. spark-sql command line options
    spark-sql is a convenient tool to run the Hive Metastore service in local mode and execute queries from the command line.
    Note that the spark-sql cannot talk to the Thrift JDBC server.
    Configuration of Hive is done by placing your hive-site.xml, core-site.xml and hdfs-site.xml files in ${SPARK_HOME}/conf/.

    $ ${SPARK_HOME}/bin/spark-sql --help
    Usage: ./bin/spark-sql [options] [cli option]

    • Generic options:
      --name NAME                 A name of your application.
      
      --master MASTER_URL         local, spark://host:port, mesos://host:port, yarn, or k8s://https://host:port.
                                  (Default: local[*])
      
      --deploy-mode DEPLOY_MODE   Whether to launch the driver program locally ("client")
                                  or on one of the worker machines inside the cluster ("cluster").
                                  (Default: client)
      
      --conf PROP=VALUE           Arbitrary Spark configuration property.
      
      --properties-file FILE      Path to a file from which to load extra properties.
                                  If not specified, this will look for conf/spark-defaults.conf.
      
      --class CLASS_NAME          Your application’s main class (for Java / Scala apps).
      
      --jars JARS                 Comma-separated list of jars to include on the driver and executor classpaths.
      
      --packages                  Comma-separated list of maven coordinates of jars to include on the driver and executor classpaths.
                                  Will search the local maven repo, then maven central and any additional remote repositories given by --repositories.
                                  The format for the coordinates should be groupId:artifactId:version.
      
      --exclude-packages          Comma-separated list of groupId:artifactId,
                                  to exclude while resolving the dependencies provided in --packages
                                  to avoid dependency conflicts.
      
      --repositories              Comma-separated list of additional remote repositories to search for the maven coordinates given with --packages.
      
      --py-files PY_FILES         Comma-separated list of .zip, .egg, or .py files to place on the PYTHONPATH for Python apps.
      
      --files FILES               Comma-separated list of files to be placed in the working directory of each executor.
                                  File paths of these files in executors can be accessed via SparkFiles.get(fileName).
      
      --driver-memory MEM         Memory for driver (e.g. 1000M, 2G) (Default: 1024M).
      --driver-java-options       Extra Java options to pass to the driver.
      --driver-library-path       Extra library path entries to pass to the driver.
      --driver-class-path         Extra class path entries to pass to the driver.
                                  Note that jars added with --jars are automatically included in the classpath.
      
      --executor-memory MEM       Memory per executor (e.g. 1000M, 2G).
                                  (Default: 1G)
      
      --proxy-user NAME           User to impersonate when submitting the application.
                                  This argument does not work with --principal / --keytab.
      
      --help, -h                  Show this help message and exit.
      --verbose, -v               Print additional debug output.
      --version,                  Print the version of current Spark.

    • Cluster deploy mode only:
      --driver-cores NUM          Number of cores used by the driver, only in cluster mode.
                                  (Default: 1)

    • Spark standalone or Mesos with cluster deploy mode only:
      --supervise                 If given, restarts the driver on failure.
      
      --kill SUBMISSION_ID        If given, kills the driver specified.
      
      --status SUBMISSION_ID      If given, requests the status of the driver specified.

    • Spark standalone and Mesos only:
      --total-executor-cores NUM  Total cores for all executors.

    • Spark standalone and YARN only:
      --executor-cores NUM        Number of cores per executor.
                                  (Default: 1 in YARN mode, or all available cores on the worker in standalone mode)

    • YARN only:
      --queue QUEUE_NAME          The YARN queue to submit to.
                                  (Default: "default")
      
      --num-executors NUM         Number of executors to launch.
                                  If dynamic allocation is enabled, the initial number of executors will be at least NUM.
                                  (Default: 2)
      
      --archives ARCHIVES         Comma separated list of archives to be extracted into the working directory of each executor.
      
      --principal PRINCIPAL       Principal to be used to login to KDC, while running on secure HDFS.
      
      --keytab KEYTAB             The full path to the file that contains the keytab for the principal specified above.
                                  This keytab will be copied to the node running the Application Master via the Secure Distributed Cache,
                                  for renewing the login tickets and the delegation tokens periodically.

    • CLI options:
      --hiveconf <property=value>   Use value for given property
      
      --hivevar <key=value>         Variable subsitution to apply to hive commands. e.g. --hivevar A=B
      
      --database <databasename>     Specify the database to use
      
      -d,--define <key=value>          Variable subsitution to apply to hive commands. e.g. -d A=B or --define A=B
      
      -e <quoted-query-string>         SQL from command line
      
      -f <filename>                    SQL from files
      
      -i <filename>                    Initialization SQL file
      
      -S,--silent                      Silent mode in interactive shell
      
      -v,--verbose                     Verbose mode (echo executed SQL to the console)
      
      -H,--help                        Print help information
  2. Start spark-sql
    $ spark-sql --hiveconf "hive.metastore.uris=thrift://localhost:9083" --hiveconf "hive.exec.scratchdir=/tmp/a-folder-that-the-current-user-has-permission-to-write-in"
    Using Spark’s default log4j profile: org/apache/spark/log4j-defaults.properties
    Spark master: local[*], Application Id: local-1561208451788
    INFO SparkSQLCLIDriver: Spark master: local[*], Application Id: local-1561208451788
    spark-sql> 

    To exit spark-sql type exit;

    Note the usage of the hive configuration --hiveconf "hive.exec.scratchdir=/tmp/a-folder-that-the-current-user-has-permission-to-write-in"
    By default, the configuration "hive.exec.scratchdir" has the value to "/tmp/hive"
    If you don't initialize this configuration, you might get this error when starting spark-sql:
    java.lang.RuntimeException: The root scratch dir: /tmp/hive on HDFS should be writable. Current permissions are: rwxrwxr-x
  3. spark-shell commands
    You can use one the following commands:
    DROP
    ALTER
    CREATE
    TABLE
    INSERT
    DELETE
    TRUNCATE
    SELECT
    FROM
    ADD
    DESC
    WITH
    VALUES
    
    COMMIT
    ROLLBACK
    
    REVOKE
    GRANT
    
    DESCRIBE
    EXPLAIN
    SHOW
    USE
    
    MAP
    SET
    RESET
    START
    
    REDUCE
    REFRESH
    CLEAR
    CACHE
    UNCACHE
    DFS
    
    ANALYZE
    LIST
    
    LOCK
    UNLOCK
    MSCK
    EXPORT
    IMPORT
    LOAD

    Query: show databases
    spark-sql> show databases;
    default
    test1
    test_db
    Time taken: 1.17 seconds, Fetched 3 row(s)
© 2025  mtitek