Tables

CREATE TABLE

CREATE TABLE [IF NOT EXISTS] <table>
  (<column> <type> [NOT NULL] [ENCODING <encoding spec>], ...)
  [WITH (<property> = value, ...)];

<type> supported include:

  • BOOLEAN
  • SMALLINT
  • INT[EGER]
  • BIGINT
  • NUMERIC | DECIMAL (PRECISION[,SCALE]) max precision 19
  • FLOAT | REAL
  • DOUBLE [PRECISION]
  • [VAR]CHAR(length)
  • TEXT
  • TIME
  • TIMESTAMP
  • DATE

<encoding spec> supported include:

  • DICT: Dictionary encoding on String columns (default for TEXT columns).
  • NONE: No encoding. Only valid on TEXT columns. No Dictionary is created. Aggregate operations are not possible on this column type.
  • FIXED(bits): Fixed length encoding of integer or timestamp columns. See Fixed Encoding.

The <property> in the optional WITH clause can be

  • fragment_size: number of rows per fragment that is a unit of the table for query processing. It defaults to 32 million rows and is not expected to be changed.
  • max_rows: this property allows you to create a capped collection by setting the maximum number of rows allowed in a table. When this limit is reached, the oldest fragment is removed. The default value is 2^62.
  • page_size: number of bytes for an I/O page. The default is 1MB and does not need to be changed.
  • partitions: Set the partition strategy to either SHARDED or REPLICATED.

Example:

CREATE TABLE IF NOT EXISTS tweets (
  tweet_id BIGINT NOT NULL,
  tweet_time TIMESTAMP NOT NULL ENCODING FIXED(32),
  lat REAL,
  lon REAL,
  sender_id BIGINT NOT NULL,
  sender_name TEXT NOT NULL ENCODING DICT,
  location TEXT ENCODING DICT,
  source TEXT ENCODING DICT,
  reply_to_user_id BIGINT,
  reply_to_tweet_id BIGINT,
  lang TEXT ENCODING DICT,
  followers INT,
  followees INT,
  tweet_count INT,
  join_time TIMESTAMP ENCODING FIXED(32),
  tweet_text TEXT,
  state TEXT ENCODING DICT,
  county TEXT ENCODING DICT,
  place_name TEXT,
  state_abbr TEXT ENCODING DICT,
  county_state TEXT ENCODING DICT,
  origin TEXT ENCODING DICT);

CREATE TABLE AS SELECT

CREATE TABLE <new table name> AS (<SELECT statement>);

Creates a new table with the specified columns, copying any data that meet the criteria of the SELECT statement.

Note: MapD Core Database does not support CREATE TABLE AS SELECT in a distributed cluster.

Note: MapD Core Database does not support CREATE TABLE AS SELECT for columns with the following data types:

  • Non-encoded strings
  • Columns that contain array values
  • Fixed-encoding integers (see Fixed Encoding)

Examples:

CREATE TABLE newTable AS (SELECT * FROM oldTable);

Creates a duplicate table named newTable from the existing oldTable.

CREATE TABLE trousers AS (SELECT name, waist, inseam FROM wardrobe);

Creates a table, trousers, copying the data in the columns name, waist, and inseam from the existing table wardrobe.

CREATE TABLE options AS (SELECT star, planet FROM cosmos WHERE class='M');

Creates a table, options, copying the data in the columns star and planet from the table cosmos for planets whose class is M.

ALTER TABLE

ALTER TABLE <table> RENAME TO <table>;
ALTER TABLE <table> RENAME COLUMN <column> TO <column>;

Examples:

ALTER TABLE tweets RENAME TO retweets;
ALTER TABLE retweets RENAME COLUMN source TO device;

DROP TABLE

DROP TABLE [IF EXISTS] <table>;

Example:

DROP TABLE IF EXISTS tweets;