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

Showing content from https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-partition below:

Partitions | Databricks Documentation

Applies to: Databricks SQL Databricks Runtime

A partition is composed of a subset of rows in a table that share the same value for a predefined subset of columns called the partitioning columns. Using partitions can speed up queries against the table as well as data manipulation.

To use partitions, you define the set of partitioning column when you create a table by including the PARTITIONED BY clause.

When inserting or manipulating rows in a table Databricks automatically dispatches rows into the appropriate partitions.

You can also specify the partition directly using a PARTITION clause.

This syntax is also available for tables that don't use Delta Lake format, to DROP, ADD or RENAME partitions quickly by using the ALTER TABLE statement.

The PARTITIONED BY clause specified a list of columns along which the new table is partitioned.

PARTITIONED BY ( { partition_column [ column_type ] } [, ...] )

• partition_column

  An identifier may reference a column_identifier in the table. If you specify more than one column there must be no duplicates. If you reference all columns in the table's column_specification an error is raised.

• column_type

  Unless the partition_column refers to a column_identifier in the table's column_specification, column_type defines the data type of the partition_column.

  Not all data types supported by Databricks SQL are supported by all data sources.

Unless you define a Delta Lake table partitioning columns referencing the columns in the column specification are always moved to the end of the table.

You use the PARTITION clause to identify a partition to be queried or manipulated.

A partition is identified by naming all its columns and associating each with a value. You need not specify them in a specific order.

Unless you are adding a new partition to an existing table you may omit columns or values to indicate that the operation applies to the all matching partitions matching the subset of columns.

PARTITION ( { partition_column  [ = partition_value | LIKE pattern ] } [ , ... ] )

• partition_column

  A column named as a partition column of the table. You may not specify the same column twice.

• = partition_value

  A literal of a data type matching the type of the partition column. If you omit a partition value the specification will match all values for this partition column.

• LIKE pattern

  This form is only allowed in ALTER SHARE ADD TABLE.

  Matches the string representation of partition_column to pattern. pattern must be a string literal as used in LIKE.

SQL

> CREATE TABLE student(university STRING,
                       major      STRING,
                       name       STRING)
         PARTITIONED BY(university, major)

> CREATE TABLE professor(name STRING)
         PARTITIONED BY(university STRING,
                        department STRING);


> INSERT INTO student
         PARTITION(university= 'TU Kaiserslautern') (major, name)
         SELECT major, name FROM freshmen;


> CREATE TABLE log(date DATE, id INT, event STRING)
     USING CSV
     PARTITIONED BY (date);

> ALTER TABLE log ADD PARTITION(date = DATE'2021-09-10');

> ALTER TABLE log DROP PARTITION(date = DATE'2021-09-10');


> ALTER TABLE student DROP PARTITION(university = 'TU Kaiserslautern');

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