Amazon Redshift实用命令语句

1. 数据库管理相关命令

创建数据库

CREATE DATABASE mydatabase;

Amazon Redshift创建数据库命令除了基本形式外，还有以下几种带不同参数的形式：

带OWNER参数

可以指定数据库的所有者，通常是一个数据库用户或角色。

CREATE DATABASE mydatabase OWNER myuser;

带ENCODING参数

用于指定数据库使用的字符编码。

CREATE DATABASE mydatabase ENCODING 'UTF8';

带TEMPLATE参数

可以基于已有的模板数据库来创建新数据库。

CREATE DATABASE mydatabase TEMPLATE template1;

带LOCATION参数

在Amazon Redshift Spectrum中，可以使用LOCATION参数指定外部数据的存储位置。

CREATE DATABASE mydatabaseWITH LOCATION 's3://mybucket/mydata';

带ACL参数

可以设置访问控制列表，来控制哪些用户或角色可以访问数据库。

CREATE DATABASE mydatabaseWITH ACL ('user1=rw', 'user2=r');

删除数据库

DROP DATABASE mydatabase;

Amazon Redshift中删除数据库的命令除了基本形式外，还可以带有一些参数，以下是具体介绍：

基本形式

DROP DATABASE mydatabase; ，用于直接删除名为 mydatabase 的数据库。

带 FORCE 参数

 DROP DATABASE [IF EXISTS] mydatabase [FORCE]; 

• IF EXISTS ：可选参数，用于在删除数据库时，如果数据库不存在，不会抛出错误，而是默默忽略该操作。
• FORCE ：用于在有活动连接到要删除的数据库时，强制关闭连接并删除数据库。一般在数据库有正在使用的连接，而又需要立即删除数据库的情况下使用。

使用 FORCE 参数要谨慎，因为它会强制断开所有与目标数据库的连接，可能导致正在进行的事务被中断，数据丢失或不一致。

带 CASCADE 参数

 DROP DATABASE mydatabase CASCADE;

CASCADE 参数会自动删除依赖于要删除数据库的所有对象，如该数据库中的表、视图、函数等。如果不使用 CASCADE ，当数据库中有对象依赖关系时，删除操作可能会失败。

使用 CASCADE 时也要小心，确保了解所有相关的依赖关系，以免意外删除重要数据和对象。

修改数据库

ALTER DATABASE mydatabase RENAME TO newdatabase;

Amazon Redshift中修改数据库的命令除了上述重命名数据库外，还有以下几种常见形式及不同参数的用法：

修改数据库所有者

ALTER DATABASE mydatabase OWNER TO new_owner;

此命令用于更改数据库的所有者为指定的用户或角色。

修改数据库的参数配置

ALTER DATABASE mydatabase SET parameter_name = value;

比如可以设置数据库的时区等参数：

ALTER DATABASE mydatabase SET timezone = 'UTC';

可以根据具体需求修改其他配置参数，如 work_mem 、 search_path 等。

重置数据库的参数配置

ALTER DATABASE mydatabase RESET parameter_name;

用于将之前设置的数据库参数重置为默认值。

修改数据库的连接限制

ALTER DATABASE mydatabase CONNECTION LIMIT 100;

上述命令将数据库 mydatabase 的连接限制设置为100个，可根据实际情况调整连接数限制。

撤销数据库的连接限制

ALTER DATABASE mydatabase CONNECTION LIMIT -1;

使用 -1 表示撤销连接限制，允许无限制的连接。

2. 表管理相关命令

创建表

CREATE TABLE <表名> (id INT PRIMARY KEY,name VARCHAR(100),created_at TIMESTAMP
);

Amazon Redshift中创建表命令 CREATE TABLE 有多种形式和参数可用于定义表的结构和属性，以下是一些常见的形式和示例：

基本形式

CREATE TABLE <表名> (column1 data_type [column_constraint],column2 data_type [column_constraint],...[table_constraint]
);

指定分布键

CREATE TABLE <表名> (id INT PRIMARY KEY,name VARCHAR(100),created_at TIMESTAMP
)
DISTKEY (id);

指定排序键

CREATE TABLE <表名> (id INT PRIMARY KEY,name VARCHAR(100),created_at TIMESTAMP
)
SORTKEY (created_at);

采用复合排序键

CREATE TABLE <表名> (id INT PRIMARY KEY,name VARCHAR(100),created_at TIMESTAMP,updated_at TIMESTAMP
)
SORTKEY (created_at, updated_at);

临时表

CREATE TEMP TABLE <表名> (id INT,name VARCHAR(100)
);

外部表

CREATE EXTERNAL TABLE <表名> (id INT,name VARCHAR(100)
)
LOCATION 's3://my-bucket/my-data/'
FORMAT AS PARQUET;

基于现有表结构创建新表

CREATE TABLE <新表名>
LIKE <表名>;

基于查询结果创建新表

CREATE TABLE <新表名>
AS
SELECT column1, column2,...
FROM <表名>
WHERE <条件>;

删除表

DROP TABLE <表名>;

在Amazon Redshift中， DROP TABLE 命令用于删除表，其完整语法及不同形式如下：

基本形式

• DROP TABLE [IF EXISTS] <表名> [CASCADE | RESTRICT];

各参数解释及对应形式

• IF EXISTS ：此参数用于避免在删除不存在的表时抛出错误。如 DROP TABLE IF EXISTS <表名>; ，若表存在则删除，不存在也不会报错。
• CASCADE ：使用 CASCADE 会自动删除依赖于要删除表的其他对象，如视图、函数等。例如 DROP TABLE <表名> CASCADE; ，若表有相关依赖对象，会一并删除。
• RESTRICT ： RESTRICT 是默认选项，若表有依赖对象，删除操作会被限制并报错。如 DROP TABLE <表名> RESTRICT; ，若表存在依赖，操作将失败。

修改表结构

• 添加列

  ALTER TABLE <表名> ADD COLUMN age INT;
  

在Amazon Redshift中， ALTER TABLE ADD COLUMN 命令用于向现有表中添加新列，除了基本形式外，还有以下几种常见形式及不同参数的用法：

添加具有默认值的列

可以在添加列时为其指定默认值，当插入数据时若未为该列提供值，则会使用默认值。例如：

ALTER TABLE <表名> ADD COLUMN created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

添加可为空或不可为空的列

默认情况下，添加的列可以为空值。若要指定列不能为空，可以使用 NOT NULL 约束。例如：

ALTER TABLE <表名> ADD COLUMN email VARCHAR(255) NOT NULL;

添加列并指定位置

可以指定新列在表中的位置，使用 AFTER 关键字将新列添加到指定列之后。例如：

ALTER TABLE <表名> ADD COLUMN phone_number VARCHAR(20) AFTER last_name;

如果想把新列添加到表的开头，可以使用 FIRST 关键字。例如：

ALTER TABLE <表名> ADD COLUMN new_column INT FIRST;

添加计算列

可以根据表中其他列的值计算得出新列的值。例如：

ALTER TABLE <表名> ADD COLUMN total_amount DECIMAL(10,2) GENERATED ALWAYS AS (price * quantity) STORED;

上述示例中， total_amount 列是根据 price 列和 quantity 列计算得出的，并且结果会存储在表中。

• 删除列

  ALTER TABLE <表名> DROP COLUMN age;
  

在Amazon Redshift中，删除列的 ALTER TABLE 命令除了基本形式外，还有以下几种带有不同参数的形式：

级联删除和限制删除

• 级联删除（CASCADE）：使用 CASCADE 参数时，会自动删除与要删除列相关的所有依赖对象，如约束、视图等。示例：

ALTER TABLE <表名> DROP COLUMN age CASCADE;

• 限制删除（RESTRICT）： RESTRICT 是默认行为，如果列存在依赖关系，删除操作会被阻止并报错。示例：

ALTER TABLE <表名> DROP COLUMN age RESTRICT;

基于条件的删除

• 使用IF EXISTS：可以使用 IF EXISTS 子句来避免在列不存在时出现错误。如果列存在则删除，不存在则不进行任何操作，也不会报错。示例：

ALTER TABLE <表名> DROP COLUMN IF EXISTS age;

• 修改列的数据类型

  ALTER TABLE <表名> ALTER COLUMN name TYPE VARCHAR(200);
  

在Amazon Redshift中，修改列的数据类型的 ALTER TABLE 命令有以下几种常见形式及不同参数的用法：

使用 USING 子句指定转换方式

• 当需要将列的数据从一种类型转换为另一种类型时，可能需要使用 USING 子句来指定转换的方式。例如，将一个整数列转换为字符串列，并在转换过程中添加特定的格式：

ALTER TABLE <表名>
ALTER COLUMN my_column TYPE VARCHAR(200)
USING my_column::VARCHAR(200) || ' some text';

• 若要将日期列转换为时间戳类型，可以这样写：

ALTER TABLE <表名>
ALTER COLUMN date_column TYPE TIMESTAMP
USING date_column::TIMESTAMP;

结合 CASCADE 或 RESTRICT

• CASCADE ：如果修改列的数据类型可能会影响到其他对象（如视图、函数等），使用 CASCADE 会自动级联修改这些相关对象。例如：

ALTER TABLE <表名>
ALTER COLUMN my_column TYPE NEW_TYPE
CASCADE;

• RESTRICT ：使用 RESTRICT 时，如果修改列的数据类型会导致与其他对象冲突，则操作会被限制并报错。这是默认行为，通常可以省略不写，但显式写出可以更清晰地表达意图：

ALTER TABLE <表名>
ALTER COLUMN my_column TYPE NEW_TYPE
RESTRICT;

对字符类型列修改长度

• 对于字符类型的列，可以修改其长度。例如，将 VARCHAR 列的长度从100增加到200：

ALTER TABLE <表名>
ALTER COLUMN my_column TYPE VARCHAR(200);

• 如果要将 CHAR 列的长度缩短，也可以类似操作，但要注意可能会截断数据：

ALTER TABLE <表名>
ALTER COLUMN my_column TYPE CHAR(10);

修改数字类型的精度和范围

• 对于数字类型，如 NUMERIC ，可以修改其精度和范围。例如，将 NUMERIC(5,2) 修改为 NUMERIC(8,4) ：

ALTER TABLE <表名>
ALTER COLUMN my_column TYPE NUMERIC(8,4);

• 若要将 INT 类型改为 BIGINT 以增加数据存储范围：

ALTER TABLE <表名>
ALTER COLUMN my_column TYPE BIGINT;

3. 数据操作相关命令

加载数据

COPY <表名>
FROM 's3://mybucket/mydatafile.csv'
CREDENTIALS 'aws_access_key_id=YOUR_KEY;aws_secret_access_key=YOUR_SECRET'
DELIMITER ',' CSV;

Amazon Redshift的 COPY 命令用于从各种数据源加载数据，除了上述基本形式外，还有以下常见的不同参数形式：

从S3加载并指定压缩格式

COPY <表名>
FROM 's3://mybucket/mydatafile.csv.gz'
CREDENTIALS 'aws_access_key_id=YOUR_KEY;aws_secret_access_key=YOUR_SECRET'
DELIMITER ',' CSV GZIP;

这里使用 GZIP 指定了数据文件是gzip压缩格式。

从S3加载并处理日期格式

COPY <表名>
FROM 's3://mybucket/mydatafile.csv'
CREDENTIALS 'aws_access_key_id=YOUR_KEY;aws_secret_access_key=YOUR_SECRET'
DELIMITER ',' CSV DATEFORMAT 'YYYY-MM-DD';

DATEFORMAT 参数用于指定日期数据的格式。

从S3加载并跳过文件头

COPY <表名>
FROM 's3://mybucket/mydatafile.csv'
CREDENTIALS 'aws_access_key_id=YOUR_KEY;aws_secret_access_key=YOUR_SECRET'
DELIMITER ',' CSV IGNOREHEADER 1;

IGNOREHEADER 1 表示跳过CSV文件的第一行，即文件头。

从S3加载并指定区域

COPY <表名>
FROM 's3://mybucket/mydatafile.csv'
CREDENTIALS 'aws_access_key_id=YOUR_KEY;aws_secret_access_key=YOUR_SECRET'
DELIMITER ',' CSV REGION 'us-west-2';

REGION 参数用于指定S3存储桶所在的区域。

从本地文件加载数据

COPY <表名>
FROM 'file:///path/to/mydatafile.csv'
DELIMITER ',' CSV;

这里使用 file:// 协议从本地文件系统加载数据。

从其他数据库加载数据

COPY <表名>
FROM 'jdbc:postgresql://source_database_url/mydb'
CREDENTIALS 'user=source_user;password=source_password'
FORMAT JDBC
SELECT * FROM source_table;

通过 JDBC 格式从其他数据库（如PostgreSQL）加载数据， SELECT 语句用于指定从源表中选择数据。

导出数据

UNLOAD ('SELECT * FROM <表名>')
TO 's3://mybucket/mydatafile_'
CREDENTIALS 'aws_access_key_id=YOUR_KEY;aws_secret_access_key=YOUR_SECRET'
DELIMITER ',' ADDQUOTES;

Amazon Redshift的 UNLOAD 命令用于导出数据，除了上述基本形式外，还有以下几种常见的带不同参数的形式：

按特定条件导出

可以使用 WHERE 子句添加筛选条件来导出特定数据。

UNLOAD ('SELECT * FROM <表名> WHERE column_name = ''value''')
TO 's3://mybucket/mydatafile_'
CREDENTIALS 'aws_access_key_id=YOUR_KEY;aws_secret_access_key=YOUR_SECRET'
DELIMITER ',' ADDQUOTES;

压缩数据导出

通过 GZIP 参数对导出数据进行压缩。

UNLOAD ('SELECT * FROM <表名>')
TO 's3://mybucket/mydatafile_'
CREDENTIALS 'aws_access_key_id=YOUR_KEY;aws_secret_access_key=YOUR_SECRET'
DELIMITER ',' ADDQUOTES GZIP;

加密数据导出

使用 ENCRYPTION 参数来加密导出数据。

UNLOAD ('SELECT * FROM <表名>')
TO 's3://mybucket/mydatafile_'
CREDENTIALS 'aws_access_key_id=YOUR_KEY;aws_secret_access_key=YOUR_SECRET'
DELIMITER ',' ADDQUOTES ENCRYPTION 'true';

以特定格式导出

指定 FORMAT 参数来设置导出文件格式，如 CSV 或 PARQUET 。

UNLOAD ('SELECT * FROM <表名>')
TO 's3://mybucket/mydatafile_'
CREDENTIALS 'aws_access_key_id=YOUR_KEY;aws_secret_access_key=YOUR_SECRET'
FORMAT CSV DELIMITER ',' ADDQUOTES;-- 导出为PARQUET格式
UNLOAD ('SELECT * FROM <表名>')
TO 's3://mybucket/mydatafile_'
CREDENTIALS 'aws_access_key_id=YOUR_KEY;aws_secret_access_key=YOUR_SECRET'
FORMAT PARQUET;

并行导出

利用 MAXFILES 参数控制导出文件数量，实现并行导出。

UNLOAD ('SELECT * FROM <表名>')
TO 's3://mybucket/mydatafile_'
CREDENTIALS 'aws_access_key_id=YOUR_KEY;aws_secret_access_key=YOUR_SECRET'
DELIMITER ',' ADDQUOTES MAXFILES 10;

跳过头部导出

使用 HEADER 参数决定是否包含表头，配合 SKIP 可跳过指定行数。

UNLOAD ('SELECT * FROM <表名>')
TO 's3://mybucket/mydatafile_'
CREDENTIALS 'aws_access_key_id=YOUR_KEY;aws_secret_access_key=YOUR_SECRET'
DELIMITER ',' ADDQUOTES HEADER SKIP 1;

4. 数据查询和分析命令

数据检索

SELECT * FROM <表名>;

条件查询

SELECT * FROM <表名> WHERE age > 25;

数据聚合

SELECT department, COUNT(*) AS employee_count, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

数据排序

SELECT * FROM <表名> ORDER BY created_at DESC;

连接查询

SELECT a.id, a.name, b.salary
FROM employees a
JOIN salaries b ON a.id = b.employee_id;

5. 索引和视图管理

创建视图

CREATE VIEW employee_summary AS
SELECT department, COUNT(*) AS employee_count, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

在Amazon Redshift中，创建视图的命令 CREATE VIEW 有多种形式和参数可用于满足不同的需求，以下是一些常见的形式：

基本创建视图

CREATE VIEW view_name AS
SELECT column1, column2,...
FROM <表名>
WHERE <条件>;

带WITH CHECK OPTION子句

可确保通过视图进行的插入或更新操作符合视图定义中的条件。

CREATE VIEW view_name AS
SELECT column1, column2,...
FROM <表名>
WHERE <条件>
WITH CHECK OPTION;

带WITH (security_barrier)选项

可防止用户通过视图访问其无权访问的数据。

CREATE VIEW view_name WITH (security_barrier) AS
SELECT column1, column2,...
FROM <表名>
WHERE <条件>;

创建临时视图

仅在当前会话中存在，会话结束后自动删除。

CREATE TEMP VIEW temp_view_name AS
SELECT column1, column2,...
FROM <表名>
WHERE <条件>;

基于多个表创建视图

CREATE VIEW view_name AS
SELECT t1.column1, t2.column2,...
FROM table1 t1
JOIN table2 t2 ON t1.join_column = t2.join_column
WHERE <条件>;

带表达式或函数的视图

CREATE VIEW view_name AS
SELECT column1, column2, function(column3) AS calculated_column
FROM <表名>
WHERE <条件>;

删除视图

DROP VIEW employee_view;

在Amazon Redshift中， DROP VIEW 命令用于删除视图，其完整语法及不同参数形式如下：

基本形式

DROP VIEW [ IF EXISTS ] view_name [,…] [ CASCADE | RESTRICT ];

各参数解析及示例

• IF EXISTS ：可选项，用于在删除视图时避免因视图不存在而报错。如 DROP VIEW IF EXISTS employee_summary; ，若 employee_summary 视图存在则删除，不存在也不会报错。
• view_name ：指定要删除的视图名称，可同时删除多个，用逗号分隔，如 DROP VIEW view1, view2, view3; 。
• CASCADE ：可选项，会自动删除依赖于该视图的其他对象。如 DROP VIEW employee_summary CASCADE; ，若有其他视图或对象依赖 employee_summary 视图，会一并删除。
• RESTRICT ：默认选项，若视图有依赖对象，则不允许删除。如 DROP VIEW employee_summary RESTRICT; ，若 employee_summary 视图存在依赖，会提示错误，阻止删除。

索引优化（注意：Redshift不支持传统的索引）

在Redshift中，通常通过选择合适的分布键（DISTKEY）和排序键（SORTKEY）来优化查询性能。

CREATE TABLE <表名> (id INT,name VARCHAR(100),created_at TIMESTAMP
)
DISTKEY(id)
SORTKEY(created_at);

6. 系统管理和监控命令

查询执行计划

EXPLAIN SELECT * FROM <表名> WHERE age > 25;

7. 安全和访问控制命令

创建用户

CREATE USER myuser WITH PASSWORD 'mypassword';

删除用户

DROP USER myuser;

授权权限

GRANT SELECT, INSERT ON TABLE my_table TO myuser;

撤销权限

REVOKE SELECT, INSERT ON TABLE my_table FROM myuser;

8. 高级功能命令

数据压缩（自动化）

Redshift通过其列式存储技术自动压缩数据。你可以通过指定压缩编码来控制每列的压缩方式：

CREATE TABLE <表名> (id INT ENCODE bytedict,name VARCHAR(100) ENCODE lzo,created_at TIMESTAMP ENCODE delta
);

并行查询（自动优化）

Amazon Redshift使用MPP架构支持并行查询处理，通常无须额外的命令来启用并行处理，但你可以通过调整查询的参数来影响并行度：

SET enable_parallel_query TO true;

外部表和数据集成

通过与Amazon S3、AWS Glue等服务的集成，Redshift支持无缝的数据流转和转换。

例如，创建外部表（与Amazon S3集成）：

CREATE EXTERNAL SCHEMA ext_schema
FROM DATA CATALOG
DATABASE 'my_external_db'
IAM_ROLE 'arn:aws:iam::account-id:role/myRedshiftRole'
CREATE EXTERNAL DATABASE IF NOT EXISTS;CREATE EXTERNAL TABLE ext_schema.<表名> (id INT,name VARCHAR(100),created_at TIMESTAMP
)
STORED AS PARQUET
LOCATION 's3://mybucket/mydata/';