В этом руководстве вы узнаете, как создать простой ETL для перемещения данных электронных таблиц Google между системами с помощью нескольких строк кода.
Представьте себе это: вы собираете источники данных для создания нового отчета и понимаете, что некоторые наборы данных все еще обновляются вручную вашими заинтересованными сторонами и хранятся в таблицах Google ... звучит знакомо?
В этом случае у вас есть два варианта: либо вы запустите ускоренный курс, чтобы научить своих менее технических коллег работе с SQL и хранилищами данных, либо вы сами автоматизируете процесс с помощью Python.
В этом руководстве вы узнаете, как извлекать наборы данных из электронной таблицы Google с помощью Python, подключившись к Google Drive API, а затем сохранить их в таблице базы данных с помощью пакета SQLAlchemy.
Пошагово следуя руководству, вы сможете автоматизировать другую утомительную задачу за короткое время с помощью нескольких строк кода. Давай начнем!
Взаимодействие с Google Drive API
Чтобы извлечь данные из электронной таблицы Google без использования пользовательского интерфейса, вам необходимо вызвать API Google Диска через Python.
Есть много способов добиться этого, но шаги, описанные ниже, скорее всего, потребуют меньше времени и кода:
- Перейдите к консоли Google API и создайте новый проект. Все API, которые вы включите с этого момента, станут частью этого проекта. При необходимости вы, конечно, можете создать несколько проектов или отредактировать текущий.
- Теперь на верхней панели выберите «Включить API и службы», затем найдите API Google Диска и API Google Таблиц. Оба они должны быть включены.
- Если вы включили API Google Диска в последний раз, оставайтесь в соответствующем разделе и выберите «Создать учетные данные». Кроме того, вы можете найти список включенных API в нижней части основной панели инструментов проекта.
- На экране «Добавить учетные данные в свой проект» просто выберите те же параметры, что и на снимке экрана. Например, будьте осторожны при выборе «Other Non-UI», поскольку вы будете взаимодействовать с API через Python.
- На последнем этапе создайте учетную запись службы, чтобы сделать возможным взаимодействие с API, и выберите «Владелец» в качестве роли. Затем вам будет разрешено загрузить учетные данные учетной записи службы в формате JSON.
6. Откройте загруженный файл и скопируйте адрес электронной почты клиента. Затем перейдите к электронной таблице, которую вы хотите прочитать с помощью Python, нажмите Файл> Поделиться и вставьте электронное письмо, чтобы предоставить доступ пользователю службы.
7. * Необязательно: переместите файл с учетными данными в ту же папку, в которой будет размещаться записная книжка / сценарий Python. Этот шаг не является обязательным, но облегчит вам жизнь при получении учетных данных.
Вот и все, скучные шаги! Также хорошая новость заключается в том, что вам нужно выполнить эти шаги только один раз, а затем вы можете продолжать использовать тот же адрес электронной почты клиента для доступа к любому количеству электронных таблиц, сколько захотите . Давайте напишем немного кода сейчас.
Чтение данных из G-таблиц
Вам нужно скачать и импортировать следующие пакеты:
Затем вы можете просто использовать следующую пользовательскую функцию для извлечения данных из электронной таблицы:
Что делает эта функция:
- В первых двух строках он определяет объем вашего вызова, который в данном случае должен взаимодействовать с Google Диском, и указывает путь к файлу с учетными данными, загруженными ранее. В этом случае предполагается, что файл был перемещен в ту же папку, что и записная книжка Python.
- В следующих двух строках он извлекает учетные данные по указанному пути и использует их для авторизации доступа к электронным таблицам Google через Python.
- В последних двух строках авторизованный клиент используется для открытия электронной таблицы, выбора определенного листа и извлечения всех записей в виде словаря. В конце концов словарь преобразуется в DataFrame.
Функция принимает два аргумента (spreadsheet_name и sheet_num). Ниже он был протестирован на электронной таблице с именем Retail_Transactions, которая включает только один лист:
gsheet2df(‘Retail_Transactions’, 0)
Помните, что в Python индексация начинается с нуля, поэтому для выбора первого листа в электронной таблице вам нужно будет установить sheet_num = 0. Запустив функцию, мы получим следующий результат:
Теперь вы можете применить все необходимые преобразования к этому df, прежде чем записывать его в целевую таблицу.
Https://towardsdatascience.com/write-datasets-from-g-sheet-to-your-database-with-python-bd2c2643f958
Запись данных в таблицу базы данных
Предположим, что после некоторых манипуляций вы, наконец, можете записать эти данные в таблицу хранилища данных, чтобы использовать их как часть вашего решения для создания отчетов. В этом руководстве показано, как подключиться к базе данных Redshift с помощью пакета SQLAlchemy, но тот же код применяется к другим базам данных (вам просто нужно немного изменить строку подключения).
В частности, вы должны использовать приведенный ниже фрагмент для создания строки подключения, а также для установления соединения с Redshift:
Как только соединение установлено, вы можете создать таблицу в предпочтительной схеме, используя метод pandas .to_sql:
Также не забудьте предоставить доступ к таблице пользователю вашей БД, чтобы вы могли запрашивать данные с помощью SQL.
Теперь весь сценарий можно запланировать с помощью Airflow или периодически запускать с помощью альтернативного инструмента планирования, чтобы полностью автоматизировать процесс!
Заключение
В этом руководстве вы узнали, как автоматизировать процесс, который извлекает данные из электронной таблицы Google в pandas DataFrame и в конечном итоге записывает данные в таблицу, размещенную в предпочитаемом вами хранилище данных. Объем используемого кода очень ограничен, что позволяет адаптировать шаги к различным вариантам использования.
Знаете ли вы, что есть еще более быстрый способ подключения к электронным таблицам Google и получения данных из них? Если да, поделитесь, пожалуйста, в комментариях!
