如何在 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屋！