Загрузка данных в BigQuery

Загрузка данных Woocommerce в Google BigQuery

Иван Никитин и партнеры ➢ Новости ➢ WordPress ➢ WooCommerce ➢ Загрузка данных Woocommerce в Google BigQuery

В этой статье показывается пошаговый процесс загрузки в Google BigQuery большого объёма данных о товарах и заказах из WooCommerce с помощью инструментов командной строки wp-cli и JSON-парсера jq.

У нас возникла задача провести анализ и построить графики продаж товаров помесячно за все время работы компании. По ряду причин мы не могли использовать данные Google Analytics, поэтому было решено использовать данные WooCommerce. Однако по непонятным причинам у WooCommerce в WC Admin нет возможности выгрузить список проданных товаров с датами продаж и реальной ценой продажи. Поэтому у нас было три пути:

  1. Найти и использовать какой-нибудь плагин выгрузки данных;
  2. Выгрузить данные непосредственно из БД WooCommerce;
  3. Выгрузить данные с помощью wp-cli.

Использование плагина я отмёл сразу, так как всевозможные плагины экспорта хорошо работают, когда данных не очень много, а у нас десятки тысяч заказов, поэтому как минимум экспорт будет долгим.

Непосредственный запрос в базу данных WordPress/WooCommerce я оставил как крайнюю меру, это чистый хардкор, и к нему надо прибегать только, если другие методы не сработали.

В результате я остановился на выгрузке данных через wp-cli.

Что такое wp-cli?

wp-cli это инструмент командной строки Linux, который позволяет выполнять практически любые операции с WordPress и WooCommerce прямо из терминала сервера. Это очень удобный инструментарий, незаслуженно игнорируемый веб-мастерами. Меня он много-много раз выручал и поэтому именно с него я и решил начать решение нашей задачи.

Установка wp-cli

Если wp-cli у вас не установлен, то чего мы ждем? Ставится он буквально в четыре команды:

curl -O https://raw.githubusercontent.com/wp-cli/builds/gh-pages/phar/wp-cli.phar
php wp-cli.phar –info
chmod +x wp-cli.phar
sudo mv wp-cli.phar /usr/local/bin/wp

После этого можно проверить работу wp-cli. Для этого надо обязательно переключиться в папку сайта на сервере и, например, посмотреть список пользователей WordPress:

wp user list

Выгрузка заказов и купленных товаров из WooCommerce

В общем виде получить список заказов с помощью wp-cli можно командой:

wp wc shop_order list --user=ivan

Здесь важно отметить, что из-за особенности работы WC желательно всегда указывать пользователя WP, от имени которого выполняется запрос в WooCommerce.

Видно, что вывод очень громоздкий, поэтому желательно указать список полей, которые нам реально нужны. Это можно сделать с помощью ключа —fields.

Совет. Всегда запрашивайте только нужные поля! Это значительно упрощает работу.

Команда получается такой:

wp wc shop_order list --user=ivan --fields="id,date_created,line_items"

Тут появляются два момента:

  1. Во-первых, поле товаров в заказе (собственно, что нам и нужно!) возвращается в виде массива JSON.
  2. Команда всегда возвращает до ста записей. Если нужно больше ста, то следует повторять ее с ключом пагинации —page=2 и т. п.

Поскольку товары возвращаются в виде массива JSON, то нам только и остается выбрать выходной формат всей команды как JSON, а не CSV. Это делается с помощью ключа —format=json. И саму команду выполнять bash-скриптом много раз, запрашивая разные страницы по сто товаров.

Итак, фрагмент скрипта выглядит следующим образом:

cd /var/www/site.ru
for i in {0001..1000}; 
do
    output_file="$data_folder/data-$i.json"
    echo " Шаг $i"
    wp wc shop_order list \
        --user=ivan --per_page=100 --page=$i \
        --fields="id,date_created,line_items" \
        --format=json \
        > $output_file 2 >/dev/null
 
    # Если файл пустой, прекращаем цикл
    filesize=$(stat -c '%s' $output_file)
    if [ $filesize -le 100 ]; then
        break
    fi
done

Этот фрагмент сформирует в рабочей папке какое-то количество JSON файлов по сто заказов в каждом. В нашем случае скрипт выполнил чуть более 500 итераций и это заняло около получаса.

Совет. Запускайте свой скрипт в терминальном мультиплексоре tmux. Это позволит отключаться от сервера, пока он работает.

Манипуляции с JSON c помощью процессора jq

В отличие от CSV JSON-файлы просто так не склеить друг с другом. Поэтому нам нужно средство  обработки JSON, и оно есть – командный процессор jq. Это очень мощная штука, хотя и не такая простая (но и не такая уж и сложная). Потратив несколько вечеров на изучение документации и набивании руки на примерах, вы получите очень удобный и мощный инструмент для работы с JSON.

Установка jq

Для начала установим его:

sudo apt update
sudo apt install jq

Теперь попробуем проверить его работу:

echo '{ "a" : "test", "b" : 1000.0 }' | jq

Удобный, легко читаемый результат! Но jq не только может форматировать вывод, но и выполнять практические любые манипуляции с данными JSON. Например, если вы посмотрите сгенерированные файлы, вы увидите, что там много лишнего, например, поля meta_data, image, parent_name, которые нам явно не нужны. Давайте удалим их сразу же после генерации, до сохранения в файл:

wp wc … | jq 'del(.[].line_items[].meta_data) | del(.[].line_items[].image) | del(.[].line_items[].parent_name)' > $output_file

Объединить два файла с массивами JSON можно с помощью команды:

jq -s '.[0] + .[1]' file1.json file2.json > result.json

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

# Результирующий файл
result_file="$data_folder/result.json"
echo "[]" > $result_file

# Переключимся в работую папку
cd /var/www/site.ru
for i in {001..10000}; 
do
    output_file="$data_folder/data-$i.json"
    echo " Шаг $i"
    { wp wc shop_order list \
        --user=ivan --per_page=100 --page=$i \
        --fields="id,date_created,line_items" \
        --format=json \
        | jq 'del(.[].line_items[].meta_data) | del(.[].line_items[].image) | del(.[].line_items[].parent_name)'; } \
        > $output_file 2>/dev/null

    # Если файл пустой, прекращаем цикл
    filesize=$(stat -c '%s' $output_file)
    if [ $filesize -le 100 ]; then
        break
    fi

    # Запись в результирующий файл
    jq -s '.[0] + .[1]' $result_file $output_file > $result_file.tmp
    rm $result_file
    mv $result_file.tmp $result_file
done

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

Загрузка JSON данных в новую таблицу Google BigQuery

Для загрузки в BigQuery требуется так называемый формат JSONL (JSON New Line), который представляет собой фактически несколько JSON-объектов с данными одного ряда таблицы, каждый из который расположен на одной строке исходного файла.

{ "field1" : "data1" }
{ "field1" : "data2" }
{ "field1" : "data3" }

У нас же исходный файл – это массив объектов:

[
	{ "field1" : "data1" },
	{ "field1" : "data2" },
	{ "field1" : "data3" }
]

Этот массив надо преобразовать в JSONL. Делается это простой командой jq:

cat result.json | jq -c '.[]' > bigquery.json

Надо отметить высокую скорость работы jq: в нашем случае на преобразование 80-мегабайтного JSON файла потребовалось около секунды.

Вот теперь можно загружать данные в GBQ!

Совет: предварительно заархивируйте файл с помощью gzip! Файл большой и этим вы резко увеличите скорость его отправки в BigQuery.

Заархивировать файл можно командой:

gzip -v9 bigquery.json

Для загрузки в консоли BigQuery выберите свой датасет и щелкните по трем точкам справа от его имени и выберите Create table. В открывшемся окне установите:

  • Create table from – Upload
  • Select file – выберите свой файл bigquery.json.gz
  • File Format – JSONL
  • Table – введите имя таблицы
  • Schema – Auto detect
Новая таблица BigQuery
Создание таблицы BigQuery

Можно загружать.

Ошибки при загрузке данных WooCommerce в BigQuery

Но в нашем случае я тут же получил ошибку загрузки. Связана она была с полем товара price. Дело в том, что у нас в России товары редко продаются с копейками, и поэтому поле price:2000 в первой же строчке BigQuery определил в схеме таблицы как INTEGER, но, если в данных где-то встретится price:250.5, то тут возникает ошибка несоответствия типов FLOAT и INTEGER.

Схема таблицы в BigQuery генерируется по первой записи, поэтому я попробовал указать явный FLOAT в поле цены первого ряда: price:2000.0. Однако фокус не прошел. Ошибка парсинга данных на стороне BigQuery.

Примерно с час я крутил данные и так и сяк, а потом осенило! Решение оказалось таким же простым, как и неожиданным: поле цены нужно указать строкой, в которой находится число FLOAT! Спишем это на ЛГБТ наклонности программистов Корпорации Как-бы-Добра.

Итак, перед загрузкой данных откройте свой файл редактором и в первой строчке (ТОЛЬКО в первой строчке) найдите ВСЕ строки price. Заключите значения в двойные кавычки и допишите к ним дробную часть, например

...,"price":"2000.0"...

Совет. В качестве редактора используйте VS Code. На 80-мегабайном файле Notepad++ уже откровенно сдыхал, а обычный блокнот, как хомячка, разрывало в клочья.

После чего сохраните файл и загружайте его в BigQuery.

Как результат мы получили требуемую таблицу в BigQuery, с которой можно теперь удобно работать.

Данные в BigQuery
Загруженные данные в BigQuery

Заключение

Если вам интересна тема работы с данными в BigQuery, напишите в комментариях, у нас много таких кейсов, тогда я буду понимать, что их надо тоже публиковать.

Похожие записи

Добавить комментарий