Страницы: 1
RSS
Экспорт данных из Power Query в БД PostgreSQL
 
Добрый день! Коллеги, прошу вашу помощь с Power Query. Требуется экспортировать данные, полученные в ходе запроса PQ в базу данных PostgreSQL.
В файле примере есть запрос PQ_price это данные которые необходимо экспортировать в БД и запрос PSQL_price это таблица в БД в которую нужно импортировать данные из запроса PQ_price.
Гугл говорит, что в PQ существуют функции для работы с БД, для которых можно передать свой SQL запрос в качестве аргумента. Например, PostgreSQL.Database и Value.NativeQuery. Но не смог найти использование этих функций именно для экспорта данных. Подскажите возможно ли это?
Изменено: Murderface_ - 15.07.2020 14:11:15 (Файл с решением)
 
Доброе время суток
Цитата
Murderface_ написал:
Требуется экспортировать данные, полученные в ходе запроса PQ в базу данных PostgreSQL.
Почитайте про UPDATE, INSERT DDL операторы. А как работает
Цитата
Murderface_ написал:
Value.NativeQuery
можно почитать Passing Parameters To SQL Queries With Value.NativeQuery() In Power Query And Power BI

P. S. Microsoft крайне не рекомендует пользоваться таким подходом.
 
anvg, спасибо за статью, почитаю.

P.S.
Ура! Получилось  :)  Файл с решением в первом сообщении. Сначала удаляются данные в таблице из БД с помощью команды TRUNCATE, а затем через INSERT INTO загружаются новые данные из PQ.
 
Murderface_, а теперь попробуйте добавить не стирая  :D
F1 творит чудеса
 
Максим, а в чём проблема, если есть первичный ключ в таблицах?
 
anvg, Максим Зеленский, первичный ключ есть. Если существует более "правильный" способ решения данной задачи, то прошу подсказать его  ;)

Цитата
anvg написал:
P. S. Microsoft крайне не рекомендует пользоваться таким подходом.
Скажите пожалуйста почему?
Изменено: Murderface_ - 16.07.2020 07:05:15
 
Цитата
Максим Зеленский написал:
попробуйте добавить не стирая
Легко  ;)  
Изменено: Murderface_ - 17.07.2020 06:54:01
 
Murderface_,
Ваш Чудесный код переделал в функцию которой можно скормить любую таблицу перед выгрузкой:
жаль, что таблицы в SQL все-таки придется создавать вручную.
Типы данных, блин, не сопоставимы CREATE TABLE в SQL по-моему не получится автоматизировать, так как нужно вручную задавать тип данных SQL.
Код
(Table)=>
let

    source = Table,
    combine = Table.CombineColumns(
          Table.TransformColumnTypes(
          source,
          List.Transform( 
          Table.ColumnNames(source),
          each{_, type text}), 
          "ru-RU"
          ),
   Table.ColumnNames(source),
   Combiner.CombineTextByDelimiter("','", QuoteStyle.None),"Сведено"),

    prefix = Table.TransformColumns(combine, {{"Сведено", each "('" & _, type text}}),

    sufix = Table.TransformColumns(prefix, {{"Сведено", each _ & "')", type text}}),
    transpose = Table.Transpose(sufix),
    result = Table.CombineColumns(transpose,Table.ColumnNames(transpose),Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Сведено")[Сведено]{0}
in
    result
   

Изменено: lostandleft - 16.07.2020 20:18:58
 
Цитата
lostandleft написал:
жаль, что таблицы в SQL все-таки придется создавать вручную.
Какие ваши доказательства? Или у той учётной записи есть права только DML, но не на DDL? А в чём смысл создания произвольной таблицы средствами Power Query? Поделитесь знаниями, интересно же :)
Цитата
Murderface_ написал:
Скажите пожалуйста почему?
Потому что без первичного ключа вы можете не один раз вставить одно и тоже. С ним, да, повторная вставка данных для одного и того же значения ключа будет отвергнута, но талантов же у нас много.... Главное напихать и отчитаться :)
Изменено: anvg - 16.07.2020 20:23:42
 
anvg,
Эм... у меня 9 таблиц, которые нужно перекинуть в SQL базу, зачем мне для каждой таблицы вручную все дело прописывать, менять названия колонок, если можно использовать универсальную функцию для всех 9 таблиц?
Можете научить меня  создавать таблицу не вручную, автоматически прописывая тип данных для каждой из 9 таблиц на сервере SQL?
В них и тип текст, и дата, и интервал времени, и десятичное число.
было бы круто ели Вы создадите функцию которая создает в SQL таблицу на сервере из имеющейся, например используя выше представленный пример.
Изменено: lostandleft - 16.07.2020 22:30:25
 
Цитата
lostandleft написал:
перекинуть в SQL базу
БД это строго структурированное типизированное и, как правило, индексированное хранилище данных, которое заранее спроектировано для определённых целей, со строго настроенными взаимосвязями между таблицами и ограничениями. Не следует БД рассматривать, как этакий безразмерный Excel. Поэтому просто так создавать в БД таблицу, подобно листу в Excel по существу не имеет смысла. Вы так и не ответили, зачем создавать таблицу?
Изучите SQL CREATE TABLE Statement. Только учтите, что как правило каждый движок БД имеет свой диалект. ANSI DDL поддерживается, но на уровне определения типов данных столбцов таблицы, могут быть заметные различия.
 
Цитата
anvg написал:
Вы так и не ответили, зачем создавать таблицу
Знаете, я бы с удовольствием ответил. но понимаю сути вопроса.
Загрузка на сервер нужна сугубо для целей общего пользования, и просто безразмерного хранилища данных. Связи между таблицами пока что, не будут существовать.
Мне нужно организовать обмен данными между несколькими пользователями, и хранение объема информации для анализа.
Большее пока что не требуется.
Кстати для этих целей почти идеально подошла надстройка MySqlforExcel  но есть свои минусы, об этом писал в соседней ветке.
Просто выгрузка на сервер, поправка небольшая таблиц, не больше. Много пользователей может использовать данные.
Каждому пользователю можно выдать привилегии кому только читать таблицу, кому и запись в нее делать.
Удобно ведь.
Изменено: lostandleft - 16.07.2020 23:37:07
 
anvg, большой проблемы нет, если есть первичный ключ, и знаете, что делать и т.п.
В основном МС не рекомендует использовать PQ для прямой загрузки в БД из-за потенциально многократного обращения PQ к источнику (которым в данном случае является БД) из-за специфики языка. Можно легко напортачить.
Хотя в SSAS он, кажется, как раз для этого и используется.
F1 творит чудеса
 
Цитата
lostandleft написал:
код переделал в функцию
Кстати можно даже проще сделать. Вот тут произвел конвертацию в текст в формате JSON, делается это также с помощью функции getJSON, но она состоит всего из 2х команд. Для дальнейшей загрузки данных в БД PostgreSQL из формата JSON я в запросе SQL_INSERT_INTO использовал json_populate_recordset. К сожалению не знаю какой из 2х вариантов загрузки данных быстрее.
 
Murderface_, Подскажите пожалуйста, а Вы не знаете ли Как внутри запроса к SQL серверу задать Имя пользователя и пароль вручную?
В сроке именно это возможно сделать?
 
Цитата
Максим Зеленский написал:
Хотя в SSAS он, кажется, как раз для этого и используется.
Ну, да, также как и Power BI и Power Pivot model. Только это не последовательное добавление новых/обновление существующих данных, просто кэширование результата выполнения запроса Power Query.
Изменено: anvg - 17.07.2020 12:07:46
 
Цитата
lostandleft написал:
Как внутри запроса к SQL серверу задать Имя пользователя и пароль вручную
Скорее всего никак. У меня подключение к серверу происходит на первом шаге запроса SQL_INSERT_INTO с помощью функции PostgreSQL.Database, которая в качестве агрументов может принимать адрес сервера и имя базы данных. Логин и пароль вводятся при первом подключении к БД, появится кнопка "Изменить учетные данные" куда вводится логин/пароль, Эти данные хранятся локально. Т. е. при запуске запроса на разных компьютерах нужно будет вводить логопасс при первом подключении к БД. Сохраненные логины/пароли хранятся в параметрах источника данных.
 
Цитата
Murderface_ написал:
Скорее всего никак
Гуглю целый день, похоже на то. У меня тоже самое.
Правда коннект к Mysql.
Не нравится мне что никак не сделать нормальное сохранение имени пользователя и пароля. К каждому юзеру придется на машину идти покдключаться к БД.
 
Цитата
lostandleft написал:
К каждому юзеру придется на машину идти покдключаться к БД.
так и задумано. Мало ли, вдруг вы захотите у конкретного Васи забрать права. Как будете это делать?
F1 творит чудеса
 
Цитата
lostandleft написал:
Как внутри запроса к SQL серверу задать Имя пользователя и пароль вручную?
Если исходить из такого подхода, то зачем
Цитата
lostandleft написал:
К каждому юзеру придется на машину идти покдключаться к БД.
Разошлите учётные данные по e-mail - пусть сами и вводят.
 
Цитата
Максим Зеленский написал:
Как будете это делать?
Планировалось конкретному Васе на сервере учетку ограничить, или убить. Это проще чем к нему идти и что-то настраивать.
Но если так задумано, будем учиться жить как правильно  :)  
 
Цитата
anvg написал:
Разошлите учётные данные по e-mail - пусть сами и вводят.
Я хотел вместе с файлом сразу же все выслать и учетные данные для подключения, внутри запроса.
Чтобы пользователь просто открыл фаил и начал пользовался. Без дополнительных настроек.
Изменено: lostandleft - 17.07.2020 19:01:23
Страницы: 1
Наверх