CREATE TABLE Examples
1. Create a table in the default HDFS location (II_HDFSDATA).
Note: Examples 1 and 2 assume the database was created in the default HDFS location (for example: createdb -vii_hdfsdata customer).
CREATE TABLE customer (
custid INT NOT NULL DEFAULT 0,
zip CHAR(5) NOT NULL)
WITH NOPARTITION;
2. Create a table in the default HDFS location with 8 partitions distributed across the location:
CREATE TABLE customer (
custid INT NOT NULL DEFAULT 0,
zip CHAR(5) NOT NULL
) WITH PARTITION = (HASH ON custid 8 PARTITIONS);
3. Create a partitioned table with rows distributed evenly based on a hash value:
CREATE TABLE employee (
emp_no INTEGER NOT NULL NOT DEFAULT,
emp_name CHAR(32) NOT NULL NOT DEFAULT,
dept_no INTEGER,
emp_rating INTEGER)
WITH JOURNALING,
PARTITION = (HASH ON emp_no
16 PARTITIONS WITH LOCATION = (location1));
4. Create two partitions in db_loc1:
CREATE TABLE X (a i4 NOT NULL,
b i4 NOT NULL)
WITH LOCATION=(db_loc1),
PARTITION=(HASH ON a 2 PARTITIONS);
5. Create two partitions with location db_loc2. Location db_loc1 will be ignored:
CREATE TABLE X (a i4 NOT NULL,
b i4 NOT NULL)
WITH LOCATION=(db_loc1),
PARTITION=(HASH ON a 2 PARTITIONS WITH LOCATION=(db_loc2));
6. Create four partitions, two in location db_loc2, one in location db_loc1, and one in location db_loc3:
CREATE TABLE X (a i4 NOT NULL,
b i4 NOT NULL)
WITH LOCATION=(db_loc1),
PARTITION=(HASH ON a 2 PARTITIONS WITH LOCATION=(db_loc2),
1 PARTITION,
1 PARTITION WITH LOCATION=(db_loc3));
7. Add a new HDFS location to an existing database and create a table with no logical partitions that uses default HDFS replication based on that location.
CREATE LOCATION vhl01 WITH AREA = 'hdfs://namenode:54310/vectorwise/vhl01', USAGE = (DATABASE);
CREATE TABLE customer (
custid INT NOT NULL DEFAULT 0,
zip CHAR(5) NOT NULL
) WITH LOCATION = (vhl01), NOPARTITION;
8. Create a table in which the social security number is encrypted using AES 128-bit encryption. Do not add 16 bytes of random bits to the field to further obfuscate the encrypted value (NOSALT):
CREATE TABLE socsectab (
fname CHAR(10),
lname CHAR(20),
socsec CHAR(11) ENCRYPT NOSALT )
WITH ENCRYPTION=AES128, PASSPHRASE='this is a secret', NOPARTITION;
9. Create a table in which data for column c2, which contains salary data, is encrypted using AES 256-bit encryption. Salt is added to the field by default:
CREATE TABLE t1 (
c1 CHAR(20) NOT NULL,
c2 MONEY ENCRYPT)
WITH ENCRYPTION=AES256, PASSPHRASE='decoder ring', NOPARTITION;