Получить первую строку вложенного поля в BigQuery

Я боролся с вопросом, который кажется простым, но ускользает от меня. Я имею дело с общедоступной таблицей BigQuery для биткойнов и хочу извлечь первую транзакцию каждого добытого блока. Другими словами, чтобы заменить вложенное поле его первой строкой, как оно отображается в предварительном просмотре таблицы. Нет поля, которое может его идентифицировать, только порядок, в котором оно было сохранено в таблице.

Я выполнил следующий запрос:

#StandardSQL
SELECT timestamp,
    block_id,
    FIRST_VALUE(transactions) OVER (ORDER BY (SELECT 1))
FROM `bigquery-public-data.bitcoin_blockchain.blocks`

Но при запуске он обрабатывает 492 ГБ и выдает следующую ошибку:

Error: Resources exceeded during query execution: The query could not be executed in the allotted memory. Sort operator used for OVER(ORDER BY) used too much memory..

Это кажется таким простым, я должен что-то упустить. У вас есть идея, как справиться с такой задачей?


sql google-bigquery bigquery-standard-sql
person Dany Majard    schedule 24.06.2018    source источник

Ответы (2)


arrow_upward
4
arrow_downward 

#standardSQL
SELECT * EXCEPT(transactions),
  (SELECT transaction FROM UNNEST(transactions) transaction LIMIT 1) transaction
FROM `bigquery-public-data.bitcoin_blockchain.blocks`

Рекомендация: играя с такой большой таблицей, как эта, я бы порекомендовал создать ее меньшую версию, чтобы снизить затраты на разработку/тестирование. В этом может помочь ниже — вы можете запустить его в пользовательском интерфейсе BigQuery с целевой таблицей, которую затем будете использовать для своего разработчика. Убедитесь, что вы установили Allow Large Results и сняли Flatten Results, чтобы сохранить исходную схему.

#legacySQL
SELECT *
FROM [bigquery-public-data:bitcoin_blockchain.blocks@1529518619028]

Значение 1529518619028 берется из запроса ниже (во время выполнения) - причина, по которой я взял четыре дня назад, заключается в том, что я знаю количество строк в этой таблице, которое тогда было всего 912 по сравнению с текущими 528 858.

#legacySQL
SELECT INTEGER(DATE_ADD(USEC_TO_TIMESTAMP(NOW()), -24*4, 'HOUR')/1000)
person Mikhail Berlyant    schedule 24.06.2018
comment
Спасибо Михаил, идея сделать отдельную таблицу с ограниченным временным окном для dev очень хороша, меня какое-то время раздражало отсутствие возможности ограничить размер скана. Может быть, даже поэтому использование предела здесь ускользнуло от моего разума. - person Dany Majard; 27.06.2018
comment
Это заставило меня узнать о декораторах таблиц, которые будут полезны. - person Dany Majard; 27.06.2018

arrow_upward
3
arrow_downward

Альтернативный подход Михаила: просто запросите первую строку массива с [OFFSET(0)]:

#StandardSQL
SELECT timestamp,
    block_id,
    transactions[OFFSET(0)] first_transaction
FROM `bigquery-public-data.bitcoin_blockchain.blocks`
LIMIT 10

Эта первая строка из массива все еще имеет некоторые вложенные данные, которые вы, возможно, захотите сгладить только до их первой строки:

#standardSQL
SELECT timestamp
    , block_id
    , transactions[OFFSET(0)].transaction_id first_transaction_id
    , transactions[OFFSET(0)].inputs[OFFSET(0)] first_transaction_first_input
    , transactions[OFFSET(0)].outputs[OFFSET(0)] first_transaction_first_output
FROM `bigquery-public-data.bitcoin_blockchain.blocks`
LIMIT 1000
person Felipe Hoffa    schedule 26.06.2018
comment
Спасибо, Фелипе. Это очень полезный трюк! Что касается вложенных данных, то в различных выходных данных coinbase действительно есть интересная информация, которую я хочу сохранить. - person Dany Majard; 27.06.2018