- Загрузка CSV через Yandex Cloud
- Две таблицы из Google Sheets (Гугл Таблиц) через CSV
- Загрузка CSV через Yandex Cloud
- Публичный доступ к дашборду из Google Sheets
- Автоматизация
На самом деле, это более перспективный способ загрузки таблиц. Неважен источник, если можете получить таблицу или csv, то обновлять его можно уже автоматически. Google Sheets, Яндекс Таблицы, локальные базы данных, базы данных без белого IP, CRM и ETL системы. При этом схема более понятная, чем выгрузка в базы данных Яндекса. На этапе формирования можно склеивать таблицы, вычислять поля, обновлять не всю таблицу, а "дописывать" только новые строки. Плюсом является большой объем бесплатного хранения в Yandex Cloud (100 ГБ), Yandex Query и Yandex Datalens бесплатные. В общем, со средним объемом данных, все будет бесплатно.
Начинаем с самого начала. Открываем Datalens.
Нажимаем на Все сервисы.
Находим и выбираем Object Storage.
Нам предлагают создать наше первое облако. Нажимаем Создать.
Откроется консоль управления. В левом верхнем углу нажимаем Создать ресурс, в выпавшем списке выбираем Бакет.
Бакет - это Логическая сущность, которая помогает организовать хранение объектов. Если проще - это папка.
Откроется страница создания бакета.
Сразу откроется окно создания платежного аккаунта. Заполняем поля.
Вводим данные карты, спишут 11 рублей, подтверждаем кодом от банка.
Все заполнили, получаем грант на 4 тысячи, нажимаем Создать.
Возвращаемся к созданию бакета. вводим имя бакета. Максимальный размер ставим Без ограничения.
Без ограничения, значит, что бакет будет автоматически масштабироваться, т.е. увеличиваться. Выставляя ограничения, мы резервируем место, но нам это не надо.
Доступ на чтение объектов ставим Публичный. Нажимаем Создать бакет.
Баскет готов. Нажимаем на наш бакет.
Перетаскиваем в бакет наш csv, архивированный zip.
Открываем Все сервисы.
Находим и нажимаем Yandex Query.
В левом верхнем углу находим меню, нажимаем Ещё, в выпавшем меню выбираем Соединения.
Нажимаем + Создать.
Настройка соединения. Вписываем любое имя в Имя. Тип выбираем Object Storage. Видимость Публичная. Аутентификация бакета Приватный. Облако и каталог выбираем наше. Бакет выбираем наш. Сервисный аккаунт будет пустой - нажимаем Create new.
Откроется новая вкладка с аккаунтами. Нажимаем в правом верхнем углу Создать сервисный аккаунт.
В поле Имя вводим любое имя. В Роли в каталоге выбираем editor. Там много ролей, проще в поисковой строке набрать. Нажимаем Создать.
Появится новый сервисный аккаунт и сверху меню, выбрать Создать статический ключ доступа. В окне выбрать Создать. Появится Новый ключ. Скопируйте его на жесткий диск, он потом понадобится.
Возвращаемся на вкладку Yandex Query. Теперь в селекторе Сервисный аккаунт появился выбор свежесозданного аккаунта, выбираем его и нажимаем Проверить. Должно появиться зеленое сообщение Соединение успешно. Нажать Создать.
В левом верхнем углу находим меню, нажимаем Ещё, в выпавшем меню выбираем Привязки.
Нажимаем + Создать.
В Параметры соединения выбираем Object Storage. В Соединение выбираем наше созданное соединение.
В Параметры привязки к данным в поле Имя вписываем любое имя, лучше такое-же, как и соединение. Область видимости выбираем Публичная. В Путь ставим слэш /, так как файлы у нас будут лежать в корне бакета. Если будут папка, то соответственно путь от корня бакета. Но проще иметь один бакет чисто под таблицу.
В Сжатие выбираем gsip, так как у нас csv сжат gzip. В Формат выбираем csv_with_names, то есть таблица с заголовками. Разделитель выбираем ;, так как csv записан в таком формате.
Дальше идут колонки, которые нам нужны. Вписываем в Имя название колонки, в Тип выбираем тип данных колонки. Чекбокс Обязательно, значит, что в колонке обязательно должны быть данные, если будет хоть одна пустая ячейка, будет ошибка. Подробнее про типы данных в документации Yandex Cloud.
Будьте внимательны, если в csv формат данных вида
31.11.2022будет ошибка, дата должна быть вида2022-11-31. Конвертируйте дату перед отправкой вObject Storage.
Колонки партиционированния не заполняем. Нажимаем Предпросмотр.
В нижней части появится наша таблица. Нажимаем Создать.
В Привязках нажать эту кнопку.
В выпавшем меню выбрать SELECT.
В правом окне появится SQL запрос, скопируем его без нижней строчки LIMIT 10;, он нам понадобится при создании датасета.
Открываем Datalens.
Создать подключение
Выбираем Yandex Query
В Облако и каталог выбираем наше облако. В поле Сервисный аккаунт выбираем наш аккаунт. Уровень доступа SQL запросов ставим в Разрешить подзапросы датасетах и запросы из чартов. Нажимаем Создать подключение. Вводим название подключения.
В правом верхнем углу нажимаем Создать датасет.
Должно автоматически открыться окно Источник с SQL подзапросом.
Вставляем наш SQL запрос и нажимаем Применить.
Ну вот и все. Мы настроили трансляцию с Object Storage в Datalens.
Задаем идентификаторы, которые получили при регистрации сервисного аккаунта. Задаем имя бакета, в который будем загружать и имя файла, который надо загрузить. Возвращает список файлов, которые уже естьв бакете. На скрипт можно повесить cron или Менеджер задач для периодического выполнения.
import boto3
import gzip
import shutil
import os.path
key_id = 'идентификатор ключа' # Получили при регистрации сервисного аккаунта
access_key = 'секретный ключ' # Получили при регистрации сервисного аккаунта
bucket_name = 'имя бакета'
file_name = 'load.csv' # файл который нужно загрузить
session = boto3.session.Session(
aws_access_key_id = key_id,
aws_secret_access_key = access_key,
)
s3 = session.client(
service_name='s3',
endpoint_url='https://storage.yandexcloud.net'
)
def func_upload(filename, bucket):
with open(filename, 'rb') as f_in:
with gzip.open(filename+'.gz', 'wb') as f_out:
shutil.copyfileobj(f_in, f_out)
s3.upload_file(filename+'.gz', bucket, filename+'.gz')
# Получить список объектов в бакете
for key in s3.list_objects(Bucket=bucket)['Contents']:
print(key['Key'])
def func_upload(file_name, bucket_name)
Все подключения описаны на странице документации Object Storage. Наверняка их еще больше, так как используется популярный протокол Amazon S3.
В подключении Datalens
Google Sheetsможно подключить только один лист из Таблиц. Но что делать, если надо две или более таблиц, которые связаны друг с другом по отдельным колонкам. Очевидно, что заполнять одну большую таблицу. Но бывает, что таблицы заполняются автоматически, например CRM, или их ведет другой отдел, или сотрудники, которым "вот так удобно". Или нужен публичный доступ к дашборду. Datalens отключили материализацию и для подключенияGoogle Sheetsпубличный доступ недоступен. В общем, будем справляться сами, разработчикам не до нашей песочницы с 500 000 строками таблиц в Google Sheets, мы, если по футбольному, Первая лига. Поймите их правильно, такие объемы для баз данных это ерунда, они работают в основном на подключение к большим базам данных. Но мы как-нибудь потом попробуем с нашими Google Sheets влезть в высшую лигу.
Что делать, работаем через подключение File то есть CSV. Трудозатратнее, обновление вручную, но больше возможностей.
- можем объединять\джойнить несколько листов из Таблиц, даже из разных документов;
- полученный дашборд можно сделать публичным;
- остается возможность обновления данных вручную.
Давайте рассмотрим простой пример работы с Google Sheets через CSV. Есть Таблица с двумя листами.
Первый лист таблицы с основными показателями.
Вторая лист таблицы, в которой нас интересует колонка Стоимость
Как видно, две таблицы объединяют колонки Наименование и Название. Названия разные, но данные в них одинаковые. Главное найти колонки с идентичными данными в двух таблицах и желательно, чтобы во второй таблице эти значения были уникальными. Например, если во второй таблице Карандаш в одной строке будет стоить 20, а в другой строке Карандаш будет стоить 15, будут неверно объединены таблицы, так как программа не смотрит на другие данные. В таких случаях надо сделать уникальные названия, например второй Карандаш переименовать в Карандаш эконом. Но это делать надо до начала экспорта в Datalens.
Продолжаем. Экспортируем каждый лист в формате CSV на свой жесткий диск.
Файл -> Скачать -> Формат CSV (.csv). В диалоговом окне сохраняем на жесткий диск.
Переходим на Datalens.
Выбираем Создать подключение
Выбираем File
Нажимаем + Загрузить файлы. В диалоговом окне выбираем файлы, которые мы скачали с Таблиц, можно все сразу выделить и загрузить.
Итак, наши файлы загрузились, выбирая их в левом столбце, в правом можно посмотреть их содержимое (на предпросмотре все строки не загружаются, только часть!). Типы значений в столбцах определились правильно, то есть колонка Дата как дата (пиктограмма календарика), Заказ и Количество как числа (пиктограмма решетки), Наименование как строковое значение (пиктограмма А). Если тип определен неправильно, можно нажать пиктограмму и изменить его. Но в первую очередь это значит, что где-то в данных есть значение другого типа. Например, в столбце Количество, в одной строке вместо 0 внесено буквами ноль. Тогда интерпретатор весь столбец обозначит как строковый. Такие вещи надо править непосредственно в Google Sheets.
Нажимаем на Создать подключение в правом верхнем углу.
Вводим название нашего подключения. Важно помнить, что одинаковых имен быть не должно. То есть, нельзя назвать Подключение, Датасет, Чарт и Дашборд одним именем, будет всплывать ошибка. Добавляйте префиксы к названию: MyBIproject_connection, MyBIproject-DataSet, mybiproject_chart_gain, etc. Можно выбрать папку, куда все добро сохранять, нажав на значок >
Нажимаем Создать датасет в правом верхнем углу.
Перетаскиваем наши листы из левой колонки Таблицы в правое пустое поле.
Получаем сообщение об ошибке Датасет не прошел валидацию. Это произошло потому, что Datalens не нашел колонок с ОДИНАКОВЫМ названием. Как мы помним, у нас идентичные колонки называются Наименование и Название. Давайте исправим это. Нажимаем на сдвоенный красный кружок в правом поле.
Открывается диалог связей между нашими листами. Нажимаем на Добавить связь.
Появятся селекторы с выпадающим списком полей в наших листах. Выбираем соответственно Наименование и Название.
Нажимаем на Применить.
Чудо свершилось! Мы подружили два наших листа. Внизу страницы, в Предпросмотре, мы видим новую объединенную таблицу. Нажимаем в левом верхнем углу селектор Поля.
В этой вкладке нужно выбрать агрегацию для числовых типов, то есть как будет представляться наше число. Например, у нас есть колонка Заказы в которой у нас числа. Но, по сути, заказ - это одна единица, и когда мы хотим получить количество всех заказов, мы их считаем по одному, а не сумму номеров заказов. К тому же номера заказов у нас повторяются (смотрите исходные таблицы). Поэтому надо подсказать Datalens, что это поле у нас уникальное, поэтому выбираем в поле Агрегация напротив имени Заказ выбираем Количество уникальных. Для имен Количество и Стоимость выбираем агрегацию Сумма. Нажимаем в правом верхнем углу Сохранить. Вводим название нашего датасета.
В принципе, мы уже сделали все, что нужно. Дальше строим на основе этого Датасета строим чарты и дашборды. Но давайте проявим немного терпения и дойдем до конца, ведь нам надо еще обновлять данные и опубликовать созданный дашборд. Прошу прошения у тех, кто это уже все знает, за подробности, но кто-то первый раз будет это делать. Нажимаем в правом верхнем углу Создать чарт.
Делаем самый простой чарт. Перетаскиваем из левого поля измерение Дата в поле -> Х. Перетаскиваем из левого поля измерение Наименование в поле Цвета.
Пока столбцы у нас пустые, надо посчитать выручку по дням. Для этого создадим новый показатель Выручка. В левом поле возле поиска нажимаем значок + и выбираем Поле.
Слева у нас все поля, нажимаем на Количество, поле автоматически вставится в формулу
Слева у нас все поля, нажимаем на Количество, поле автоматически вставится в формулу. Вводим с клавиатуры символ * и нажимаем на поле Стоимость. Мы получили простую формулу где перемножаем количество товара на стоимость единицы товара. В правом верхнем поле Наименование поля вводим название Выручка. Нажимаем кнопку Создать.
Перетаскиваем из левого поля наш новый показатель Выручка в поле Y. Теперь у нас появились столбцы, с выручкой по дням и разбивкой по Наименованию. Нажимаем в правом верхнем углу Сохранить.
Вводим название чарта. В самом левом столбце с пиктограммами находим Дашборд и нажимаем на него.
В правом верхнем углу выбираем Создать дашборд. Вводим название дашборда.
В правом верхнем углу выбираем выпадающий селектор Добавить в нем выбираем Чарт.
Нажимаем Чарт Выбрать и в всплывшем окне выбираем чарт, который мы только что создали. В левом нижнем углу нажимаем Добавить.
Нажимаем в правом верхнем углу Сохранить.
Все мы создали свой дашборд. Теперь изучим его. У нас есть выручка по дням по наименованиям. Но если посмотреть на нашу первую таблицу в Google Sheets, мы увидим, что
02.11.2022 был заказ на 100 линеек, но в дашборде линеек нет. Это произошло потому, что у нас во второй таблице в Google Sheets нет Наименования Линейка, поэтому, при слиянии двух таблиц, у линейки стоимости нет, соответственно, при вычислении выручка будет 0. Давайте исправим это и проведем обновление нашего дашборда.
Заходим на Google Sheets в наш документ на второй лист. Добавляем строку со стоимостью для Линейка.
Переходим на первый лист. Добавляем строку 03.11.2022 128 Карандаш 300. Экспортируем каждый лист в формате CSV на свой жесткий диск.
Файл -> Скачать -> Формат CSV (.csv). В диалоговом окне сохраняем на жесткий диск. На жестком диске уже есть эти листы, поэтому выбираем их и заменяем.
Возвращаемся на Datalens. В самом левом столбце с пиктограммами находим Подключения и нажимаем на него. В всплывшем окне выбираем наше подключение.
В левом столбце выбираем первый лист и нажимаем на ... возле него. В менюшке выбираем Заменить. В диалоговом окне выбираем csv файл первого листа, который мы только что пересохранили с Google Sheets.
Файл обновился, и мы видим, что в левом столбце появились группы Новые файлы и Загруженные ранее. Выбираем второй файл и повторяем операцию обновления.
Все файлы обновлены, в предпросмотре справа мы видим, что строк 03.11.2022 появилась. В самом левом столбце с пиктограммами находим Дашборды и нажимаем на него (если закрыли вкладку с созданным дашбордом). Выбираем наш дашборд. Если вкладку не закрывали, просто обновите окно браузера (F5).
Мы видим, что все, что мы внесли в документе в Google Sheets, теперь у нас в дашборде. Появилась линейка, которая раньше не считалась, и заказы от 03.11.2022. По сути, создав дашборд на csv один раз, потом проводим только обновление файлов в подключении.
В самом начале в Datalens была такая фича - материализация. Это когда загруженные данные записывались на внутренние сервера Datalens, то есть делался слепок с данных, и появлялась возможность большого количества обращений к дашборду, то есть не происходили постоянно новые запросы SQL, которые нагружали бы бесплатный Datalens. Это как если товар в магазине продавался бы не с полок, а со склада. То есть продавец за каждым товаром бегал бы на склад. Пока пользователей было немного, серверы наверное вытягивали. Сейчас популярность и нагрузка растет, разработчики задумались над этим и пока экспериментируют.
Для дашбордов, созданных на основе CSV файлов доступен публичный доступ. Допустим, мы собрали информацию/статистику, сделали визуализацию ее на дашборде и хотим поделиться со всеми. Или мы создали дашборд для презентации нашего продукта/бизнеса, мы можем оправить ее любому человеку, и он без установки каких-либо программ может ее просмотреть. И для визуальной презентации нужен только браузер с подключенным интернетом. Удобно же.
Для этого достаточно включить Публичный доступ. Открываем наш дашборд.
В левом верхнем углу после символа звездочки нажимаем на ... . В выпавшем меню выбираем Публичный доступ.
В открывшемся окне, слева от надписи Доступен по ссылке, переключаем селектор. Во всплывшем окне нажимаем Продолжить.
Дашборд и все связанные объекты стали публичными. Справа от селектора появилась Публичная ссылка. Копируем ее, нажимаем Применить.
Открываем другой браузер, где не входили под учетной записью Яндекса, вставляем. Все работает.
Публичный доступ к дашборду из Google Sheets в настоящее время можно реализовать только через CSV. Реализуем все по инструкции.
Давайте будем честны. В Google Sheets таблице будет максимум миллион строк (при 5 колонках), объем - не более 100 Мб. Ежедневное добавление строк в Google Sheets не такое существенное, чтобы нельзя было раз в сутки обновить вручную. Если бы было по другому, давно бы перешли на нормальную базу данных. По аналогии с магазином, это все равно, что каждый день возить только длинномером товары в маленький сельский магазин.
Что в этом процессе можно автоматизировать?
- получение csv файлов из Google Sheets без открытия самих таблиц;
- объединение двух и более таблиц в csv по ключевым колонкам;
- склеивание в один файл csv нескольких одинаковых таблиц (когда кончается лимит на строки в одном документе, заводят следующий и таблица продолжается).