如何在 Amazon Redshift 中创建索引 [英] How to create an Index in Amazon Redshift
问题描述
我正在尝试在 Amazon Redshift 中创建索引,但收到错误
I'm trying to create indexes in Amazon Redshift but I received an error
create index on session_log(UserId);
UserId
是一个整数字段.
推荐答案
如果您尝试在 Redshift 表上创建索引(带有名称):
If you try and create an index (with a name) on a Redshift table:
create index IX1 on "SomeTable"("UserId");
你会收到错误
执行SQL命令时出现错误:在SomeTable"(UserId")上创建索引 IX1错误:Redshift 表不支持 SQL 命令在SomeTable"(UserId")上创建索引 IX1.
An error occurred when executing the SQL command: create index IX1 on "SomeTable"("UserId") ERROR: SQL command "create index IX1 on "SomeTable"("UserId")" not supported on Redshift tables.
这是因为,与其他数据仓库一样,Redshift 使用 列式存储,因此,其他 RDBMS 中使用的许多索引技术(如添加非聚集索引)不是适用.
This is because, like other data warehouses, Redshift uses columnar storage, and as a result, many of the indexing techniques (like adding non-clustered indexes) used in other RDBMS aren't applicable.
但是,您可以选择提供单个排序键 每个表,您还可以使用 影响性能分发密钥,用于对您的数据进行分片,并选择适当的压缩编码a> 为每一列最小化存储和 I/O 开销.
You do however have the option of providing a single sort key per table, and you can also influence performance with a distribution key for sharding your data, and selecting appropriate compression encodings for each column to minimize storage and I/O overheads.
例如,在您的情况下,您可以选择使用 UserId
作为排序键:
For example, in your case, you may elect to use UserId
as a sort key:
create table if not exists "SomeTable"
(
"UserId" int,
"Name" text
)
sortkey("UserId");
You might want to read a few primers like these
这篇关于如何在 Amazon Redshift 中创建索引的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!