如何为BigQuery表中的记录生成唯一键值？

Question

在BigQuery表中插入记录时，如何分配代理键？使用Sequence来生成唯一值或NextVal？

Answer 1

如果您希望在BigQuery中生成代理键值，那么最好避免使用ROW_NUMBER OVER ()选项及其变体。引用BigQuery关于代理键的帖子：

要实现ROW_NUMBER()，BigQuery需要在执行树的根节点对值进行排序，这受一个执行节点中的内存量的限制。

当你有少量的记录时，这总是会导致你遇到问题。

有两种选择：

选项1- GENERATE_UUID()

由于代理键没有业务意义，只是生成用于数据仓库的惟一键，因此您可以使用BigQuery中的GENERATE_UUID()函数调用来简单地生成它们。这为您提供了一个通用的惟一UUID，您可以将其用作代理键值。

一个缺点是这个键将是32位，而不是8字节的INT64值。因此，如果您有大量的记录，这可能会增加数据的存储大小。

散列选项2-生成唯一的

第二种选择是使用散列函数来生成唯一的has。这有点复杂，因为您需要找到列和/或随机其他输入的组合，以确保您永远不会生成相同的值两次。

一些散列函数也会输出32个字节的值，所以你不会节省存储空间，但是FARM_FINGERPRINT()散列函数会输出一个INT64值，这可以节省一些存储空间。因此，您可以使用选项1和选项2通过执行以下操作来生成唯一的整数代理键：FARM_FINGERPRINT(GENERATE_UUID())

Answer 2

下面是一种方法，它为每一行生成一个唯一的整数ID，ID根据源数据集中的某个值排序，在本例中是时间戳：

SELECT
  RANK() OVER(ORDER BY timestamp) unique_id,
  title
FROM
  [publicdata:samples.wikipedia]
LIMIT 1000

另一种方法是随机生成唯一标识符：

SELECT 
    RANK() OVER(ORDER BY random) unique_id,
    RAND() random,
    title
FROM
    [publicdata:samples.wikipedia]
LIMIT 1000

要在插入时附加这些值，请将源数据加载到BigQuery表中，然后修改上面的代码以从该表中选择(而不是维基百科)并保存结果。

Answer 3

(抱歉，还没有足够的声誉在现有答案上添加评论...)

加载数据的来源和格式是什么？如果它是CSV或JSON off GCS，那么您可以将Michael的解决方案与我们的联邦数据源(https://cloud.google.com/bigquery/federated-data-sources)配对，以便在单个操作中生成表和in，而不是同时使用load和query。