Руководство пользователя для разработчиков

Запросы копирования

Запросы на копирование позволяют использовать команду копирования PostgreSQL для эффективной передачи данных в Epsilon Metrics и из него.

Поддержка копирования разделена на две точки доступа к API:

• https://maps.epsilonmetrics.ru/user/{user}/api/v2/sql/copyfrom для загрузки данных в Epsilon Metrics
• https://maps.epsilonmetrics.ru/user/{user}/api/v2/sql/copyto для экспорта данных из Epsilon Metrics

Copy From

Команда COPY PostgreSQL очень быстрая, но требует очень точных входных данных:

• Команда COPY, которая описывает таблицу и столбцы загружаемого файла, а также формат файла.
• Файл загрузки, который точно соответствует команде COPY.

Если команда COPY, предоставленный файл и целевая таблица не совпадают, загрузка не будет выполнена.

«Copy from» копирует данные «из» вашего файла «в» Epsilon Metrics. «Copy from» использует кодирование по частям (Transfer-Encoding: chunked) для потоковой передачи файла на сервер. Это позволяет избежать ограничений, связанных с размером файла и необходимостью временного хранения: данные передаются из вашего файла прямо в базу данных.

Содержимое файла COPY должно быть отправлено в теле запроса POST.

Составление POST-запроса с частичной передачей данных является сложным, поэтому большинство разработчиков использует инструмент или язык сценариев для загрузки данных в Epsilon Metrics с помощью «copy from».

Пример

Для того чтобы таблица была доступна для чтения Epsilon Metrics, она должна иметь минимум три столбца с определенными типами данных:

• cartodb_id, первичный ключ serial
• the_geom, геометрия в проекции ESPG:4326 (долгота/широта)
• the_geom_webmercator, геометрия в проекции ESPG:3857 (веб-меркатор)

Создание новой таблицы Epsilon Metrics со всеми необходимыми триггерами и столбцами может быть сложным, поэтому вот пример:

-- создать таблицу с *обязательными* столбцами и
-- парой дополнительных
CREATE TABLE upload_example (
    the_geom geometry,
    name text,
    age integer
);

-- добавить 'cartodb_id' и 'the_geom_webmercator'
-- добавить необходимые триггеры и индексы
SELECT CDB_CartodbfyTable('upload_example');

-- Обратите внимание, что вызов CDB_CartodbfyTable отличается для пользователей организации
-- SELECT CDB_CartodbfyTable('your_org_username', 'upload_example');

Теперь вы готовы загрузить свой файл. Предположим, у вас есть CSV-файл следующего содержания:

the_geom,name,age
SRID=4326;POINT(-126 54),North West,89
SRID=4326;POINT(-96 34),South East,99
SRID=4326;POINT(-6 -25),Souther Easter,124

Команда COPY для загрузки этого файла должна указать формат файла (CSV), наличие строки заголовка перед началом данных и перечислить столбцы файла, чтобы они соответствовали столбцам таблицы.

COPY upload_example (the_geom, name, age)
FROM STDIN WITH (FORMAT csv, HEADER true)

Опция FROM STDIN говорит базе данных, что ввод будет осуществляться из потока данных, и SQL API прочтет наш загруженный файл и использует его для отправки потока.

Для выполнения загрузки вам потребуется инструмент или скрипт, который может генерировать POST-запрос с частями. Вот несколько примеров на разных языках.

Пример с использованием CURL

Утилита curl позволяет легко выполнять веб-запросы из командной строки и поддерживает загрузку POST с частями, поэтому она может использоваться для работы с конечной точкой copyfrom.

Предполагая, что вы уже создали таблицу и CSV-файл называется “upload_example.csv”:

curl -X POST \
    -H "Transfer-Encoding: chunked" \
    -H "Content-Type: application/octet-stream" \
    --data-binary @upload_example.csv \
    "https://maps.epsilonmetrics.ru/user/{user}/api/v2/sql/copyfrom?api_key={api_key}&q=COPY+upload_example+(the_geom,+name,+age)+FROM+STDIN+WITH+(FORMAT+csv,+HEADER+true)"

Для загрузки большего файла с использованием сжатия для более быстрой передачи сначала сжмите файл, а затем загрузите его с заданным кодированием содержимого:

curl -X POST  \
    -H "Content-Encoding: gzip" \
    -H "Transfer-Encoding: chunked" \
    -H "Content-Type: application/octet-stream" \
    --data-binary @upload_example.csv.gz \
    "https://maps.epsilonmetrics.ru/user/{user}/api/v2/sql/copyfrom?api_key={api_key}&q=COPY+upload_example+(the_geom,+name,+age)+FROM+STDIN+WITH+(FORMAT+csv,+HEADER+true)"

Пример на Python

Библиотека Requests для работы с HTTP делает загрузку файлов относительно краткой и лаконичной.

import requests

api_key = {api_key}
username = {api_key}
upload_file = 'upload_example.csv'
q = "COPY upload_example (the_geom, name, age) FROM STDIN WITH (FORMAT csv, HEADER true)"

url = "https://maps.epsilonmetrics.ru/user/%s/sql/copyfrom" % username
with open(upload_file, 'rb') as f:
    r = requests.post(url, params={'api_key': api_key, 'q': q}, data=f, stream=True)

    if r.status_code != 200:
        print(r.text)
    else:
        status = r.json()
        print("Success: %s rows imported" % status['total_rows'])

Немного более сложный скрипт мог бы считывать заголовки из CSV-файла и составлять команду COPY на лету.

CSV файлы и порядок столбцов

При использовании формата CSV, обратите внимание, что PostgreSQL игнорирует заголовок.

HEADER

Указывает, что файл содержит строку заголовка с именами каждого столбца в файле. На выходе первая строка содержит имена столбцов из таблицы, и на входе первая строка игнорируется. Эта опция разрешена только при использовании формата CSV.

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

Например, если ваша таблица определена так:

CREATE TABLE upload_example (
    the_geom geometry,
    name text,
    age integer
);

но ваш CSV файл имеет следующую структуру (обратите внимание, что столбцы name и age поменялись местами):

#the_geom,age,name
SRID=4326;POINT(-126 54),89,North West
SRID=4326;POINT(-96 34),99,South East
SRID=4326;POINT(-6 -25),124,Souther Easter

ваш запрос должен указать правильный порядок столбцов, независимо от заголовка в CSV:

COPY upload_example (the_geom, age, name) FROM stdin WITH (FORMAT csv, HEADER true);

Формат ответа

Успешная загрузка вернет код состояния 200 и небольшой JSON с информацией о загрузке.

{"time":0.046,"total_rows":10}

Неудачная загрузка вернет код состояния 400 и более крупный JSON с текстом ошибки PostgreSQL и трассировкой стека от SQL API.

{"error":["Unexpected field"]}

Copy To

“Copy to” копирует данные “в” ваш желаемый выходной файл “из” Epsilon Metrics.

Используя конечную точку copyto для извлечения данных, вы обходите обычное форматирование JSON, применяемое SQL API, поэтому можно выгружать больше данных быстрее. Однако имеется ограничение в том, что оно будет выводить данные только в нескольких форматах:

• PgSQL текстовый формат,
• CSV и
• PgSQL бинарный формат.

“Copy to” представляет собой простую конечную точку HTTP GET, поэтому любой инструмент или язык может быть легко использован для загрузки данных, указывая следующие параметры в URL.

Пример с использованием CURL

SQL для запуска “copy to” может указывать:

• таблицу для чтения,
• таблицу и подмножество столбцов для чтения, или
• произвольный SQL-запрос для выполнения и чтения.

В нашем примере мы прочитаем только три столбца, которые изначально загрузили:

COPY upload_example (the_geom, name, age) TO stdout WITH (FORMAT csv, HEADER true)

SQL нужно закодировать в URL перед встраиванием в команду CURL, поэтому итоговый результат выглядит так:

curl \
    --output upload_example_dl.csv \
    --compressed \
    "https://maps.epsilonmetrics.ru/user/{user}/api/v2/sql/copyto?q=COPY+upload_example+(the_geom,name,age)+TO+stdout+WITH(FORMAT+csv,HEADER+true)&api_key={api_key}"

Пример на Python

Python код для “copy to” очень прост, потому что вызов HTTP - это простой get. Единственная сложность в этом примере заключается в конце, где результат возвращается блок за блоком, чтобы избежать загрузки всего содержимого в память перед записью в файл.

import requests

api_key = {api_key}
username = {api_key}
download_filename = 'download_example.csv'
q = "COPY upload_example (the_geom, name, age) TO stdout WITH (FORMAT csv, HEADER true)"

# request the download
url = "https://maps.epsilonmetrics.ru/user/%s/sql/copyto" % username
r = requests.get(url, params={'api_key': api_key, 'q': q}, stream=True)
r.raise_for_status()

with open(download_filename, 'wb') as handle:
    for block in r.iter_content(1024):
        handle.write(block)
print("Downloaded to: %s" % savefilename)

Ограничения

Существует ограничение по времени в 5 часов для конечных точек /copyfrom и /copyto. Идея заключается в том, что на практике операции COPY не должны ограничиваться обычным таймаутом запроса.

Кроме того, вы не можете превысить свою квоту базы данных в операциях /copyfrom. Попытка сделать это приведет к ошибке DB Quota exceeded, и транзакция COPY FROM будет откатываться.

Максимальный размер полезной нагрузки /copyfrom, который можно сделать в одном запросе POST, ограничен 2 ГБ. Любая попытка превышения этого размера приведет к ошибке COPY FROM maximum POST size of 2 GB exceeded, и снова вся транзакция будет откатываться.