Spark SQL----CREATE TABLE

07-19 1545阅读

Spark SQL----CREATE TABLE

  • 一、描述
  • 二、创建 DATASOURCE 表
    • 2.1 描述
    • 2.2 语法
    • 2.3 参数
    • 2.4 数据源交互
    • 2.5 例子
    • 三、创建HIVEFORMAT表
      • 3.1 描述
      • 3.2 语法
      • 3.3 参数
      • 3.4 例子
      • 四、CREATE TABLE LIKE
        • 4.1 描述
        • 4.2 语法
        • 4.3 参数
        • 4.4 例子

          一、描述

          CREATE TABLE语句用于在现有数据库中定义一个表。

          Spark SQL----CREATE TABLE
          (图片来源网络,侵删)

          二、创建 DATASOURCE 表

          2.1 描述

          CREATE TABLE语句使用数据源定义一个新表。

          2.2 语法

          CREATE TABLE [ IF NOT EXISTS ] table_identifier
              [ ( col_name1 col_type1 [ COMMENT col_comment1 ], ... ) ]
              USING data_source
              [ OPTIONS ( key1=val1, key2=val2, ... ) ]
              [ PARTITIONED BY ( col_name1, col_name2, ... ) ]
              [ CLUSTERED BY ( col_name3, col_name4, ... ) 
                  [ SORTED BY ( col_name [ ASC | DESC ], ... ) ] 
                  INTO num_buckets BUCKETS ]
              [ LOCATION path ]
              [ COMMENT table_comment ]
              [ TBLPROPERTIES ( key1=val1, key2=val2, ... ) ]
              [ AS select_statement ]
          

          注意,USING子句和AS SELECT子句之间的子句可以按任意顺序出现。例如,可以在TBLPROPERTIES后面写COMMENT表注释。

          2.3 参数

          • table_identifier

            指定一个表名称,可以选择使用数据库名称对其进行限定。

            语法:[database_name.]table_name

          • USING data_source

            数据源是用于创建表的输入格式。数据源可以是CSV、TXT、ORC、JDBC、PARQUET等。

          • OPTIONS

            将被注入存储属性的数据源的选项。

          • PARTITIONED BY

            分区是根据指定的列在表上创建的。

          • CLUSTERED BY

            在表上创建的分区将根据指定的存储列被存储到固定的存储桶中。

            注:Bucketing是一种优化技术,它使用bucket(和Bucketing列)来确定数据分区并避免数据shuffle。

          • SORTED BY

            指定bucket列的顺序。可选地,在SORTED BY子句中的任何列名之后,可以使用ASC表示升序,也可以使用DESC表示降序。如果未指定,则默认为ASC。

          • INTO num_buckets BUCKETS

            指定存储桶编号,该编号用于CLUSTERED BY子句。

          • LOCATION

            存储表数据的目录的路径,可以是分布式存储(如HDFS等)上的路径。

          • COMMENT

            用于描述表的字符串文字。

          • TBLPROPERTIES

            用于标记表定义的键值对列表。

          • AS select_statement

            该表是使用select语句中的数据填充的。

            2.4 数据源交互

            数据源表的作用类似于指向基础数据源的指针。例如,你可以使用JDBC数据源在Spark中创建一个表“foo”,该表指向MySQL中的一个表”bar“。当你读/写表“foo”时,你实际上读/写了表“bar”。

            一般来说,CREATE TABLE是在创建一个“指针”,你需要确保它指向现有的东西。一个例外是文件源,如parquet、json。如果你没有指定LOCATION,Spark将为你创建一个默认的表位置。

            对于CREATE TABLE AS SELECT with LOCATION,如果给定位置作为非空目录存在,Spark将抛出分析异常。如果spark.sql.legacy.allowNonEmptyLocationInCTAS设置为true,spark将用输入查询的数据覆盖基础数据源,以确保创建的表包含与输入查询完全相同的数据。

            2.5 例子

            --Use data source
            CREATE TABLE student (id INT, name STRING, age INT) USING CSV;
            --Use data from another table
            CREATE TABLE student_copy USING CSV
                AS SELECT * FROM student;
              
            --Omit the USING clause, which uses the default data source (parquet by default)
            CREATE TABLE student (id INT, name STRING, age INT);
            --Use parquet data source with parquet storage options
            --The columns 'id' and 'name' enable the bloom filter during writing parquet file,
            --column 'age' does not enable
            CREATE TABLE student_parquet(id INT, name STRING, age INT) USING PARQUET
                OPTIONS (
                  'parquet.bloom.filter.enabled'='true',
                  'parquet.bloom.filter.enabled#age'='false'
                );
            --Specify table comment and properties
            CREATE TABLE student (id INT, name STRING, age INT) USING CSV
                COMMENT 'this is a comment'
                TBLPROPERTIES ('foo'='bar');
            --Specify table comment and properties with different clauses order
            CREATE TABLE student (id INT, name STRING, age INT) USING CSV
                TBLPROPERTIES ('foo'='bar')
                COMMENT 'this is a comment';
            --Create partitioned and bucketed table
            CREATE TABLE student (id INT, name STRING, age INT)
                USING CSV
                PARTITIONED BY (age)
                CLUSTERED BY (Id) INTO 4 buckets;
            --Create partitioned and bucketed table through CTAS
            CREATE TABLE student_partition_bucket
                USING parquet
                PARTITIONED BY (age)
                CLUSTERED BY (id) INTO 4 buckets
                AS SELECT * FROM student;
            --Create bucketed table through CTAS and CTE
            CREATE TABLE student_bucket
                USING parquet
                CLUSTERED BY (id) INTO 4 buckets (
                WITH tmpTable AS (
                    SELECT * FROM student WHERE id > 100
                )
                SELECT * FROM tmpTable
            );
            

            三、创建HIVEFORMAT表

            3.1 描述

            CREATE TABLE语句使用Hive格式定义一个新表。

            3.2 语法

            CREATE [ EXTERNAL ] TABLE [ IF NOT EXISTS ] table_identifier
                [ ( col_name1[:] col_type1 [ COMMENT col_comment1 ], ... ) ]
                [ COMMENT table_comment ]
                [ PARTITIONED BY ( col_name2[:] col_type2 [ COMMENT col_comment2 ], ... ) 
                    | ( col_name1, col_name2, ... ) ]
                [ CLUSTERED BY ( col_name1, col_name2, ...) 
                    [ SORTED BY ( col_name1 [ ASC | DESC ], col_name2 [ ASC | DESC ], ... ) ] 
                    INTO num_buckets BUCKETS ]
                [ ROW FORMAT row_format ]
                [ STORED AS file_format ]
                [ LOCATION path ]
                [ TBLPROPERTIES ( key1=val1, key2=val2, ... ) ]
                [ AS select_statement ]
            

            请注意,列定义子句和AS SELECT子句之间的子句可以按任何顺序出现。例如,可以在TBLPROPERTIES之后编写COMMENT table_comment。

            3.3 参数

            • table_identifier

              指定一个表名称,可以选择使用数据库名称对其进行限定。

              语法:[ database_name. ] table_name

            • EXTERNAL

              表是使用LOCATION提供的路径定义的,不使用此表的默认位置。

            • PARTITIONED BY

              分区是根据指定的列在表上创建的。

            • CLUSTERED BY

              在表上创建的分区将根据为分桶指定的列分桶为固定的分桶。

              注:Bucketing是一种优化技术,它使用bucket(和Bucketing列)来确定数据分区并避免数据shuffle。

            • SORTED BY

              指定bucket列的顺序。可选地,在SORTED BY子句中的任何列名之后,可以使用ASC表示升序,也可以使用DESC表示降序。如果未指定,则默认为ASC。

            • INTO num_buckets BUCKETS

              指定存储桶编号,该编号用于CLUSTERED BY子句。

            • row_format

              指定输入和输出的行格式。有关更多语法详细信息,请参阅HIVE FORMAT。

            • STORED AS

              表存储的文件格式,可以是TEXTFILE、ORC、PARQUET等。

            • LOCATION

              存储表数据的目录的路径,可以是分布式存储(如HDFS等)上的路径。

            • COMMENT

              用于描述表的字符串文字。

            • TBLPROPERTIES

              用于标记表定义的键值对列表。

            • AS select_statement

              该表是使用select语句中的数据填充的。

              3.4 例子

              --Use hive format
              CREATE TABLE student (id INT, name STRING, age INT) STORED AS ORC;
              --Use data from another table
              CREATE TABLE student_copy STORED AS ORC
                  AS SELECT * FROM student;
              --Specify table comment and properties
              CREATE TABLE student (id INT, name STRING, age INT)
                  COMMENT 'this is a comment'
                  STORED AS ORC
                  TBLPROPERTIES ('foo'='bar');  
              --Specify table comment and properties with different clauses order
              CREATE TABLE student (id INT, name STRING, age INT)
                  STORED AS ORC
                  TBLPROPERTIES ('foo'='bar')
                  COMMENT 'this is a comment';
              --Create partitioned table
              CREATE TABLE student (id INT, name STRING)
                  PARTITIONED BY (age INT)
                  STORED AS ORC;
              --Create partitioned table with different clauses order
              CREATE TABLE student (id INT, name STRING)
                  STORED AS ORC
                  PARTITIONED BY (age INT);
              --Use Row Format and file format
              CREATE TABLE student (id INT, name STRING)
                  ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
                  STORED AS TEXTFILE;
              --Use complex datatype
              CREATE EXTERNAL TABLE family(
                      name STRING,
                      friends ARRAY,
                      children MAP,
                      address STRUCT
                  )
                  ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ESCAPED BY '\\'
                  COLLECTION ITEMS TERMINATED BY '_'
                  MAP KEYS TERMINATED BY ':'
                  LINES TERMINATED BY '\n'
                  NULL DEFINED AS 'foonull'
                  STORED AS TEXTFILE
                  LOCATION '/tmp/family/';
              --Use predefined custom SerDe
              CREATE TABLE avroExample
                  ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe'
                  STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
                      OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'
                  TBLPROPERTIES ('avro.schema.literal'='{ "namespace": "org.apache.hive",
                      "name": "first_schema",
                      "type": "record",
                      "fields": [
                              { "name":"string1", "type":"string" },
                              { "name":"string2", "type":"string" }
                          ] }');
              --Use personalized custom SerDe(we may need to `ADD JAR xxx.jar` first to ensure we can find the serde_class,
              --or you may run into `CLASSNOTFOUND` exception)
              ADD JAR /tmp/hive_serde_example.jar;
              CREATE EXTERNAL TABLE family (id INT, name STRING)
                  ROW FORMAT SERDE 'com.ly.spark.serde.SerDeExample'
                  STORED AS INPUTFORMAT 'com.ly.spark.example.serde.io.SerDeExampleInputFormat'
                      OUTPUTFORMAT 'com.ly.spark.example.serde.io.SerDeExampleOutputFormat'
                  LOCATION '/tmp/family/';
              --Use `CLUSTERED BY` clause to create bucket table without `SORTED BY`
              CREATE TABLE clustered_by_test1 (ID INT, AGE STRING)
                  CLUSTERED BY (ID)
                  INTO 4 BUCKETS
                  STORED AS ORC
              --Use `CLUSTERED BY` clause to create bucket table with `SORTED BY`
              CREATE TABLE clustered_by_test2 (ID INT, NAME STRING)
                  PARTITIONED BY (YEAR STRING)
                  CLUSTERED BY (ID, NAME)
                  SORTED BY (ID ASC)
                  INTO 3 BUCKETS
                  STORED AS PARQUET
              

              四、CREATE TABLE LIKE

              4.1 描述

              CREATE TABLE语句使用现有表或视图的定义/元数据定义一个新表。

              4.2 语法

              CREATE TABLE [IF NOT EXISTS] table_identifier LIKE source_table_identifier
                  USING data_source
                  [ ROW FORMAT row_format ]
                  [ STORED AS file_format ]
                  [ TBLPROPERTIES ( key1=val1, key2=val2, ... ) ]
                  [ LOCATION path ]
              

              4.3 参数

              • table_identifier

                指定一个表名称,可以选择使用数据库名称对其进行限定。

                语法:[ database_name. ] table_name

              • USING data_source

                数据源是用于创建表的输入格式。数据源可以是CSV、TXT、ORC、JDBC、PARQUET等。

              • ROW FORMAT

                SERDE用于指定自定义SerDe或DELIMITED子句,以便使用本机SerDe。

              • STORED AS

                表存储的文件格式,可以是TEXTFILE、ORC、PARQUET等。

              • TBLPROPERTIES

                指定了必须设置的表属性,如created.by.user, owner等。

              • LOCATION

                存储表数据的目录的路径,可以是分布式存储(如HDFS等)上的路径。创建外部表的位置。

                4.4 例子

                -- Create table using an existing table
                CREATE TABLE Student_Dupli like Student;
                -- Create table like using a data source
                CREATE TABLE Student_Dupli like Student USING CSV;
                -- Table is created as external table at the location specified
                CREATE TABLE Student_Dupli like Student location  '/root1/home';
                -- Create table like using a rowformat
                CREATE TABLE Student_Dupli like Student
                    ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
                    STORED AS TEXTFILE
                    TBLPROPERTIES ('owner'='xxxx');
                
VPS购买请点击我

文章版权声明:除非注明,否则均为主机测评原创文章,转载或复制请以超链接形式并注明出处。

目录[+]