Загрузка данных JSON в AWS Redshift приводит к значениям NULL

Я пытаюсь выполнить операцию загрузки/копирования для импорта данных из файлов JSON в ведро S3 непосредственно в Redshift. Операция COPY завершается успешно, и после COPY таблица имеет правильное количество строк/записей, но каждая запись NULL!

Требуется ожидаемое количество времени для загрузки, команда COPY возвращает OK, консоль Redshift сообщает об успешных результатах и никаких ошибок... но если я выполню простой запрос из таблицы, он возвращает только значения NULL.

JSON очень прост + плоский и отформатирован правильно (согласно примерам, которые я нашел здесь: http://docs.aws.amazon.com/redshift/latest/dg/r_COPY_command_examples.html)

В принципе, это одна строка на строку, отформатированная как:

{ "col1": "val1", "col2": "val2", ... }
{ "col1": "val1", "col2": "val2", ... }
{ "col1": "val1", "col2": "val2", ... }

Я пробовал такие вещи, как переписывание схемы на основе значений и типов данных, найденных в объектах JSON, а также копирование из несжатых файлов. Я подумал, что, возможно, JSON правильно разбирался при загрузке, но он должен, вероятно, вызвать ошибку, если объекты не могут быть проанализированы.

Моя команда COPY выглядит так:

copy events from 's3://mybucket/json/prefix' 
with credentials 'aws_access_key_id=xxx;aws_secret_access_key=xxx'
json 'auto' gzip;

Любое руководство будет оценено! Спасибо.

Ответ 1

Итак, я обнаружил причину - это не было бы очевидно из описания, которое я представил в своем первоначальном сообщении.

Когда вы создаете таблицу в Redshift, имена столбцов преобразуются в строчные. Когда вы выполняете операцию COPY, имена столбцов чувствительны к регистру.

Входные данные, которые я пытался загрузить, используют camelCase для имен столбцов, поэтому, когда я выполняю COPY, столбцы не совпадают с определенной схемой (которая теперь использует все имена в нижнем регистре)

Однако операция не вызывает ошибку. Он просто оставляет NULL во всех столбцах, которые не совпадают (в этом случае все они)

Надеюсь, это поможет кому-то избежать такой же путаницы!

Ответ 2

В случаях, когда объекты данных JSON не соответствуют именам столбцов, вы можете использовать файл JSONPaths для сопоставления элементов JSON со столбцами, как упоминалось в TimZ, и описывается здесь

Ответ 3

COPY сопоставляет элементы данных в исходных данных JSON со столбцами в целевой таблице путем сопоставления ключей объекта или имен в источнике пары имя/значение с именами столбцов в целевой таблице. соответствие регистрозависимо. Имена столбцов в таблицах Amazon Redshift всегда в нижнем регистре, поэтому при использовании опции matching auto, соответствующей JSON имена полей также должны быть строчными. Если ключи имени поля JSON не все строчные, вы можете использовать файл JSONPaths для явного сопоставления столбца имена для ключей имен полей JSON.

Решением будет использование jsonpath

Пример json:

{
"Name": "Major",
"Age": 19,
"Add": {
"street":{
"st":"5 maint st",
"ci":"Dub"
},
"city":"Dublin"
},

"Category_Name": ["MLB","GBM"]

}

Пример таблицы:

(
name varchar,
age int,
address varchar,
catname varchar
);

Пример jsonpath:

{
"jsonpaths": [
"$['Name']",
"$['Age']",
"$['Add']",
"$['Category_Name']"
]
}

Пример кода копирования:

copy customer --redshift code
from 's3://mybucket/customer.json'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
json from 's3://mybucket/jpath.json' ; -- Jsonpath file to map fields

Примеры взяты из здесь