Как перенести данные из электронных таблиц в ваш хранилище данных

После того как ваше будет построено, подавляющее большинство ваших данных будет поступать из других SaaS-инструментов, внутренних баз данных или платформ данных клиентов (CDP). Но есть еще один незамеченный герой в наборе инструментов аналитической инженерии: скромная электронная таблица.

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

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

В общем, перед выбором одного из этих инструментов стоит задать себе несколько вопросов о ваших данных:

• Кто в вашей компании будет загружать данные?
• Имеет ли они постоянный формат?
• Как часто они будут изменяться?
• Каков размер набора данных?
• Нужно ли отслеживать изменения?
• Откуда поступают файлы?

Давайте рассмотрим некоторые из предложений, которые помогут вам загрузить ваши электронные таблицы в ваше хранилище данных.

dbt seeds

dbt поставляется с встроенным загрузчиком csv (seeds), который позволяет заполнять ваше хранилище данных любыми файлами, которые вы помещаете в папку seeds вашего проекта. Он автоматически определяет типы данных из содержимого вашего файла, но вы всегда можете переопределить это, предоставив явные инструкции в вашем файле dbt_project.yml.

Однако, поскольку dbt создает эти таблицы, вставляя строки по одной, он не работает эффективно в больших масштабах (нет жесткого ограничения, но стремитесь к сотням строк, а не тысячам). Документация dbt предлагает использовать seeds для "файлов, содержащих бизнес-логику, например, список кодов стран или идентификаторов пользователей сотрудников."

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

Хорошо подходит для:

• Небольших файлов, таких как сопоставление идентификаторов сотрудников с сотрудниками
• Редко изменяемых файлов, таких как сопоставление кодов стран с названиями стран
• Данных, которые выиграют от контроля версий
• Программного контроля типов данных

Не подходит для:

• Файлов размером более 1 МБ
• Файлов, которые нуждаются в регулярных обновлениях

ETL инструменты

Очевидным выбором, если у вас есть данные для загрузки в хранилище, будет ваш существующий ETL инструмент, такой как Fivetran или Stitch, о которых я расскажу в этом разделе. Ниже приведена сводная таблица, подчеркивающая основные преимущества и недостатки некоторых вариантов инструментов для загрузки данных из электронных таблиц в ваше хранилище данных.

Сводная таблица

Опция/коннектор	Данные обновляемы после загрузки	Настраиваемые типы данных	Несколько таблиц на схему	Хорошо для больших наборов данных
dbt seeds	✅	✅	✅	❌
Fivetran Browser Upload	✅	✅	✅	✅
Fivetran Google Sheets connector	✅	❌	❌	✅
Fivetran Google Drive connector	❌	❌	✅	✅
Stitch Google Sheets integration	✅	❌	❌	✅
Airbyte Google Sheets connector	✅	❌	❌	✅

Fivetran browser upload

Загрузчик браузера Fivetran делает именно то, что заявлено: вы загружаете файл в их веб-портал, и он создает таблицу, содержащую эти данные в предопределенной схеме в вашем хранилище. С визуальным интерфейсом для изменения типов данных, это легко для любого пользователя. И с учетной записью, имеющей разрешение только на загрузку файлов, вам не нужно беспокоиться о том, что ваши заинтересованные стороны случайно что-то сломают.

Преобразование типов данных из текста в даты и числа легко в визуальном редакторе

Выбор подходящего формата даты из списка опций для их преобразования в стандартизированный формат

Приятным преимуществом загрузчика является поддержка обновления данных в таблице со временем. Если загружается файл с тем же именем и теми же столбцами, любые новые записи будут добавлены, а существующие записи (по ) будут обновлены.

Однако имейте в виду, что на эти изменения нет контроля версий или возможности их отмены; возможно, вам стоит рассмотреть возможность снимка изменений в dbt, если это вызывает беспокойство.

Также Fivetran не будет удалять записи после их создания, поэтому единственный способ удалить записи, созданные с помощью этого процесса, — это вручную удалить их из вашего хранилища. Если у вас есть разовый коннектор, рассмотрите возможность автоматического удаления этих таблиц регулярно, особенно если у вас есть проблемы с управлением PII.

Хорошо подходит для:

• Файлов, которые часто обновляются кем-то
• Позволяет любому в компании загружать файлы
• Разовые загрузки данных
• Обновление таблицы вместо создания новой
• Основные изменения типов данных (включая обработку столбцов валют)
• Большие файлы

Не подходит для:

• Отслеживания изменений в данных
• Сложных сопоставлений типов

Fivetran Google Sheets connector

Основное преимущество подключения к Google Sheets вместо статической электронной таблицы должно быть очевидным — коллеги могут изменять лист из любого места, и новые записи будут автоматически загружены в ваше хранилище. Коннектор Google Sheets от Fivetran требует некоторой дополнительной начальной настройки, но совместное редактирование может сделать усилия стоящими.

Вместо синхронизации всех ячеек в листе вы создаете именованный диапазон и подключаете Fivetran к этому диапазону. Каждый коннектор Fivetran может читать только один диапазон — если у вас несколько вкладок, вам нужно будет создать несколько коннекторов, каждый со своей схемой и таблицей в целевом хранилище. Когда происходит синхронизация, таблица будет усечена и загружена заново, так как нет первичного ключа для сопоставления.

Создание именованного диапазона в Google Sheets для синхронизации через Fivetran Google Sheets Connector

Остерегайтесь несоответствий типов данных — если кто-то введет текст в столбец, который изначально был числовым, Fivetran автоматически преобразует столбец в строковый тип, что может вызвать проблемы в ваших последующих преобразованиях. Рекомендуемое решение — явно приводить ваши типы в моделях подготовки, чтобы гарантировать, что любые нежелательные записи будут преобразованы в null.

Хорошо подходит для:

• Больших, долгоживущих документов
• Файлов, которые обновляются многими людьми (и довольно часто)

Не подходит для:

• Разовых загрузок — вам нужно создать целую схему для каждой подключенной электронной таблицы, и подготовка листа — это кропотливый процесс
• Отслеживания изменений в данных
• Документов с множеством вкладок

Fivetran Google Drive connector

Я большой поклонник коннектора Google Drive от Fivetran; в прошлом я использовал его для упрощения многих еженедельных отчетов. Он позволяет заинтересованным сторонам использовать инструмент, с которым они уже знакомы (Google Drive), вместо того чтобы иметь дело с другим набором учетных данных. Каждый файл, загруженный в определенную папку на Drive (или Box, или потребительский Dropbox), превращается в таблицу в вашем хранилище.

Fivetran добавит каждый из этих csv файлов в одну схему в вашем хранилище, что делает его идеальным для регулярных загрузок

Как и в случае с коннектором Google Sheets, типы данных столбцов определяются автоматически. Даты, в частности, капризны — если вы можете контролировать ваши входные данные, постарайтесь привести их в формат ISO 8601, чтобы минимизировать количество очистки, которую вам придется делать на другой стороне.

Я использовал два макроса из пакета dbt_utils (get_relations_by_pattern и union_relations), чтобы объединить еженедельные экспорты из других инструментов в одну модель для легкой очистки в модели подготовки. Убедитесь, что вы предоставили вашей учетной записи трансформатора разрешение на доступ ко всем таблицам в схеме (включая будущие), чтобы избежать необходимости вручную вмешиваться после каждой новой загрузки файла.

Хорошо подходит для:

• Позволяет любому в компании загружать файлы
• Еженедельные экспорты из другого инструмента
• Большие файлы
• Множество файлов (каждый будет создан как еще одна таблица в одной схеме, в отличие от интеграции Google Sheets)

Не подходит для:

• Данных, которые нужно обновить после загрузки
• Пользовательских сопоставлений типов (без дальнейшей обработки в dbt)

Stitch Google Sheets integration

Интеграция Google Sheets от Stitch немного проще в настройке, чем у Fivetran, так как она импортирует весь лист без необходимости настраивать именованные диапазоны. Помимо этого, она работает так же, с теми же преимуществами и недостатками.

Хорошо подходит для:

• Больших, долгоживущих документов
• Файлов, которые обновляются многими людьми

Не подходит для:

• Разовых загрузок — вам нужно создать целую схему для каждой подключенной электронной таблицы
• Отслеживания изменений в данных
• Документов с множеством вкладок

Airbyte Google Sheets connector

Airbyte, инструмент ETL с открытым исходным кодом и облачным решением, поддерживает коннектор источника Google Sheets, очень похожий на интеграцию Stitch и Fivetran. Вам нужно будет аутентифицировать вашу учетную запись Google с помощью OAuth или ключа учетной записи службы и предоставить ссылку на Google Sheet, который вы хотите загрузить в ваше хранилище данных. Обратите внимание, что все столбцы листа загружаются как строки, поэтому вам нужно будет явно приводить их в последующей модели. Коннектор Airbyte также поддерживает как полные обновления, так и добавления.

Хорошо подходит для:

• Больших, долгоживущих документов
• Файлов, которые обновляются многими людьми
• Команд, которые могут быть ограничены в бюджете

Не подходит для:

• Данных нестрокового типа, которые вы хотите сохранить в ваших исходных таблицах в хранилище данных

Родные интеграции хранилищ данных

Каждое из основных хранилищ данных также имеет родные интеграции для импорта данных из электронных таблиц. Хотя основы одинаковы, существуют некоторые различия между различными поставщиками хранилищ данных.

Snowflake

Опции Snowflake надежны и удобны для пользователя, предлагая как веб-загрузчик, так и массовый импортер. Веб-загрузчик подходит для небольших и средних файлов (до 50 МБ) и может использоваться для конкретных файлов, всех файлов в папке или файлов в папке, которые соответствуют заданному шаблону. Он также является наиболее независимым от поставщика, поддерживая Amazon S3, Google Cloud Storage, Azure и локальную файловую систему.

Веб-мастер загрузки данных Snowflake через блог Snowflake https://www.snowflake.com/blog/tech-tip-getting-data-snowflake/

BigQuery

BigQuery поддерживает импорт данных только из внешних источников, размещенных Google, таких как Google Drive и Google Cloud Storage (поскольку BigQuery и Sheets являются продуктами Google, BigQuery — единственная платформа в этом списке, которая имеет родную интеграцию, не требующую сторонних инструментов). Данные, на которые он ссылается, не копируются в BigQuery, но могут быть использованы в запросах, как если бы они были. Если необходимо, вы можете записать копию в BigQuery или просто оставить ее как внешний источник. Команда supercooldata написала отличное руководство по настройке Google Sheets с BigQuery.

Redshift

Как и ожидалось для продукта AWS, Redshift предпочитает импортировать CSV-файлы из S3. Как и в случае с Snowflake, это достигается с помощью команды COPY, и вы можете легко контролировать, какие файлы импортируются из исходного бакета. Использование S3 в качестве источника по сравнению с веб-загрузчиком или Google Drive означает, что этот вариант не так удобен для пользователей, не обладающих техническими навыками, но все же является отличным вариантом для синхронизации файлов, которые автоматически генерируются другими инструментами.

Databricks

Databricks также поддерживает загрузку данных, таких как электронные таблицы, из внешних облачных источников, таких как Amazon S3 и Google Cloud Storage. Кроме того, возможность загрузки данных через простой интерфейс в Databricks в настоящее время находится в публичной предварительной версии.

Заключение

Помимо уже рассмотренных вариантов, существует целый мир других инструментов, которые могут загружать данные из ваших электронных таблиц в ваше хранилище данных. Это живой документ, поэтому, если ваш предпочтительный метод не указан, пожалуйста, откройте PR, и я его рассмотрю.

Наиболее важные вещи, которые следует учитывать, — это происхождение и формат ваших файлов. Если вам нужно, чтобы ваши коллеги регулярно загружали файлы, постарайтесь предоставить им более удобный процесс; но если вам просто нужно, чтобы два компьютера общались друг с другом, или это разовый файл, который вряд ли когда-либо изменится, то более техническая интеграция вполне уместна.