A RetroSearch Logo

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Search Query:

Showing content from https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-ddl-create-table-using below:

CREATE TABLE [USING] | Databricks Documentation

Prior to Databricks Runtime 16.1 START WITH must precede INCREMENT BY.

  • REPLACE

    If specified, replaces the table and its content if it already exists. This clause is only supported for Delta and Apache Iceberg tables.

    REPLACE preserves the table history and granted privileges, row filters, and column masks.

    note

    Databricks strongly recommends using REPLACE instead of dropping and re-creating tables.

  • EXTERNAL

    If specified, creates an external table. When creating an external table you must also provide a LOCATION clause. When an external table is dropped the files at the LOCATION will not be dropped.

  • IF NOT EXISTS

    If specified and a table with the same name already exists, the statement is ignored.

    IF NOT EXISTS cannot coexist with REPLACE, which means CREATE OR REPLACE TABLE IF NOT EXISTS is not allowed.

  • table_name

    The name of the table to be created. The name must not include a temporal specification or options specification. If the name is not qualified the table is created in the current schema.

    Tables created in hive_metastore can only contain alphanumeric ASCII characters and underscores (INVALID_SCHEMA_OR_RELATION_NAME).

    Iceberg tables must be created in Unity Catalog. Creating Iceberg tables in the hive_metastore isn't supported.

  • table_specification

    This optional clause defines the list of columns, their types, properties, descriptions, and column constraints.

    If you do not define columns the table schema you must specify either AS query or LOCATION.

  • USING data_source

    data_source can be either a file-format or a federated JDBC data source.

    The file-format must be one of the following:

    For any file-format other than DELTA or ICEBERG, you must also specify a LOCATION, unless the table catalog is hive_metastore.

    The following federated JDBC sources are supported:

    When specifying a federated JDBC source, you must also specify the OPTIONS clause with the necessary connection information. See Query databases using JDBC for more information on querying federated data sources.

    The following additional file formats to use for the table are supported in Databricks Runtime:

    If USING is omitted, the default is DELTA.

    The following applies to: Databricks Runtime

    HIVE is supported to create a Hive SerDe table in Databricks Runtime. You can specify the Hive-specific file_format and row_format using the OPTIONS clause, which is a case-insensitive string map. The option_keys are:

  • table_clauses

    Optionally specify location, partitioning, clustering, options, comments, and user defined properties for the new table. Each sub clause may only be specified once.

    • SQL


    > CREATE TABLE student (id INT, name STRING, age INT);


    > CREATE TABLE edu.enrollment.student (id INT, name STRING, age INT) USING ICEBERG;


    > CREATE TABLE student_copy AS SELECT * FROM student;


    > CREATE TABLE student USING CSV LOCATION '/path/to/csv_files';


    > CREATE TABLE student (id INT, name STRING, age INT)
        COMMENT 'this is a comment'
        TBLPROPERTIES ('foo'='bar');


    > CREATE TABLE student (id INT, name STRING, age INT)
        TBLPROPERTIES ('foo'='bar')
        COMMENT 'this is a comment';


    > CREATE TABLE student (id INT, name STRING, age INT)
        PARTITIONED BY (age);


    > CREATE TABLE rectangles(a INT, b INT,
                              area INT GENERATED ALWAYS AS (a * b));


    > CREATE TABLE names(name STRING COLLATE UNICODE_CI);


    > CREATE TABLE names(name STRING, first_name STRING, id STRING COLLATE UTF8_BINARY) DEFAULT COLLATION UNICODE_CI;


    > CREATE TABLE IF NOT EXISTS ora_tab
      USING ORACLE
      OPTIONS (
        url '<jdbc-url>',
        dbtable '<table-name>',
        user '<username>',
        password '<password>'
    );

    > SELECT * FROM ora_tab;

    RetroSearch is an open source project built by @garambo | Open a GitHub Issue

    Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

    HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4