Maxim Arbuzov

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

2021-07-09T11:54:25.114Z

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

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

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

Пример таблицы для импорта из Google Sheets в BigQuery

Загрузка данных через CSV

Здесь все понятно. Скачиваем наш лист с данными из гугл таблицы в формате CSV.
Открываем в нашей таблице меню Файл - Скачать - CSV файл.

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

Идем в настройки своего датасета BQ

Открываем настройки датасета в BigQuery

создаем новую таблицу: create table - upload - csv.

Создаем новую таблицу

Загружаем CSV файл в BQ

В целом, грузить данные через csv в целом можно, но есть ограничение на размер файла(100MB). Если файл больше то надо уже заморочаивтаьс яи грузить его через cloud storage.
Но если вам понадобится эти данные периодически обновлять, то такой вариант уже не подойдет, поскольку каждый раз придется пересоздавать таблицу и грузить данные заново, а это в общем-то так себе развлечение.
Поэтому есть второй более удобный метод, о котором я сам, к своему стыду, узнал недавно, потому что никогда не скролил ниже варианта загрузки через цсв.

Подключаем google sheets к bigquery

Процесс такой же, как и в случае, с CSV файлом, только в списке вариантов загрузки выбираем drive и вставляем ссылку на нашу гугл таблицу. Дополнительно можно указать диапазон который мы хотим импортировать, а также обязательно, как и в случае с csv, указываем что нам нужно пропустить первую строку в качестве заголовка и автоматически создать схему таблицы.

Подключаем Google Sheets к GBQ

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

Настройки импортированной таблицы.

Но если написать простой селект запрос то данные в результаты подтянутся.

Данные из гугл таблицы в результатах запроса бигквери

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

Главное напишите понятную инструкцию и следите чтобы данные в гугл таблицу вносили корректно. А то будет как у меня, когда в столбце для даты вместо нужного формата 2021-03-20 написали просто 20 марта. Зашибись, че.

Извлекаем email адреса из текста при помощи Google Sheets или R

2020-12-28T14:04:15.648Z

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

Это можно сделать тремя способами:

Руками - мы понятно его рассматривать не будем. Хотя если есть чужие и очень дешевые руки, а также непреодолимое желание почувствовать себя рабовладельцем - то конечно можно. Кто я такой чтобы запрещать?

Через Google Sheets - наиболее оптимальный и с низким порогом вхождения.

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

Данные для работы

В моем примере есть небольшой файл, который содержит выгрузку аккаунтов из instagram с описанием из шапки профиля(description или biography кто как называет). Из этого поля мы и будем извлекать имейлы.

Небольшой файл с примером доступен по ссылке

Регулярное выражение

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

Сама регулярка выглядит так:

[A-z0-9._%+-]+@[A-z0-9.-]+\.[A-z]{2,4}

ее мы и будем использовать в обоих случаях

Извлекаем адреса электронных почт при помощи регулярных выражений в Google Sheets

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

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

REGEXEXTRACT

Формула работает по принципу REGEXEXTRACT(текст; регулярное выражение)

Вписываем формулу в ячейку и вместо парметра "текст" ставим ссылку на нужную нам ячейку таблицы(в примере это ячейка А2). Вторым параметром пишем нашу регулярку(обязательно в кавычках)

Протягиваем по всему столбцу и получаем результат. Как видно, в тех строках, которые не содержат имейлов, формула выдает ошибку. Можно удариться в перфекционизм и добавить другую формулу IFERROR (ЕСЛИОШИБКА) которая заменит стандартный текст ошибки на какой нибудь другой, например "No email"

Конечная формула и результат выглядят так:

ЕСЛИОШИБКА(REGEXEXTRACT(A2;"[A-z0-9._%+-]+@[A-z0-9.-]+\.[A-z]{2,4}");"No email")

Готово! Наш список электронных почт готов. Остается только использовать его в правильных и непротивозаконных целях.

Извлекаем адреса электронных почт при помощи регулярных выражений в R

Здесь логика примерно такая же. Для извлечения текста из строки я буду использовать библиотеку "stringr", а для работы с CSV файлами "vroom". Устанавливаем библиотеки если еще нет и подключаем их

install.packages('stringr') # Установка
install.packages('vroom') 
library('stringr') # Подключение
library('vroom')

Загрузим CSV файл и преобразуем его в таблицу

our_table <- vroom("{PATH TO FOLDER}\\Filename.csv")

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

files <- dir(path = "{PATH TO FOLDER}", pattern = "\\.csv")
our_table <- vroom(files)

Для удобства создадим переменную regex в которую запишем уже знакомое нам регулярное выражение. Обратите внимание что в R спецзнаки экранируются через двойной обратный слеш.

regex <-'[A-z0-9\\._%+-]+@[A-z0-9\\.-]+\\.[A-z]{2,4}'

Пишем финальную формулу, в которой добавляем в существующую таблицу новый столбец для извлеченных имейлов

our_table$extract_emails <- str_extract(our_table$biography, regex)

Экспортируем получившийся результат в CSV файл

write.csv(our_table,"{PATH TO FOLDER}\\our_table.csv")

Для удобства можно отфильтровать таблицу, оставив в ней только те строки, для которых есть извлеченные имейлы.

our_table_fin <- our_table %>%
    filter(!is.na(our_table$extract_emails))

Конечны скрипт выглядит как-то так

#Устанавливаем и подключаем библиотеки
install.packages('stringr') 
install.packages('vroom') 
library('stringr') 
library('vroom')
#Грузим таблицу из CSV файла
our_table <- vroom("{PATH TO FOLDER}\\Filename.csv")
#Если файлов несколько то собираем их в одну таблицу
files <- dir(path = "{PATH TO FOLDER}", pattern = "\\.csv")
our_table <- vroom(files)
# Пишем регулярку в переменную
regex <-'[A-z0-9\\._%+-]+@[A-z0-9\\.-]+\\.[A-z]{2,4}'
#Добавляем новый столбец в таблицу и одновременно применяем функцию str_extract 
#которая достанет нужный текст по паттерну регулярного выражения
our_table$extract_emails <- str_extract(our_table$biography, regex)
# Если нужно то фильтруем таблицу оставив в ней только строки с имейлами
our_table_fin <- our_table %>%
   filter(!is.na(our_table$extract_emails))
#выгружаем результат в красивую CSVшку
write.csv(our_table_fin,"{PATH TO FOLDER}\\our_table.csv")

Вот и все. Теперь вы умеете извлекать имейлы из текста при помощи гугл таблиц, R и регулярных выражений.

Получаем значение курса валют в Google Таблицах с помощью функции IMPORTXML

2019-01-30T21:48:29.591Z

Как следует из названия функция IMPORTXML позволяет импортировать любые данные из xml файлов. Один из вариантов ее применения - импорт значений курсов валют - текущих или для конкретной даты/диапазона.

Я проверил работу функции на примере API двух банков - Приватбанка и ЦБ РФ.

Приватбанк

Текущее значение курса

XML файл с актуальным курсом находится по ссылке: https://api.privatbank.ua/p24api/pubinfo?exchange&coursid=11

Данные из него нужно парсить при помощи языка запросов XPath

Формула:

IMPORTXML("https://api.privatbank.ua/p24api/pubinfo?exchange&coursid=11";"//exchangerates/row[1]/exchangerate/@sale")

Получаем текущее значение USD. Прописав слева в ячейке А функцию TODAY получим отображение текущей даты.

Курс валют для даты или диапазона дат

Файл с архивными значениями находится по ссылке: https://api.privatbank.ua/p24api/exchange_rates?date=dd.mm.yyy,

где вместо dd.mm.yyyy необходимо подставить нужное значение даты. В данном случае это 30.01.2019. Конечная формула получения значения курса валют для даты

IMPORTXML("https://api.privatbank.ua/p24api/exchange_rates?date=30.01.2019; "//exchangerate[@currency='USD' and baseCurrency='UAH']/@saleRateNB")

Чтобы нужная дата автоматически подставлялась в формулу указываем в диапазон дат и используем функцию СЦЕПИТЬ (CONCATENATE). Для корректной работы формулы дату необходимо перевести в текстовый формат.

IMPORTXML(CONCATENATE("https://api.privatbank.ua/p24api/exchange_rates?date=";A2); "//exchangerate[@currency='USD' and @baseCurrency='UAH']/@saleRateNB")

ЦБ РФ

Ссылка для ежедневного значения

http://www.cbr.ru/scripts/XML_daily.asp

Формула имеет вид

IMPORTXML("http://www.cbr.ru/scripts/XML_daily.asp"; "//ValCurs/Valute[CharCode=""USD""]/Value")

Получаем данные для даты или диапазона

IMPORTXML("http://www.cbr.ru/scripts/XML_daily.asp?date_req=30/01/2019"; "//ValCurs/Valute[CharCode=""USD""]/Value")

Здесь также необходимо перевести дату в текстовый формат и обратить внимание на разделители.

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

Файл с примерами рабочих формул находится здесь