Добрый день! Коллеги, прошу вашу помощь с Power Query. Требуется экспортировать данные, полученные в ходе запроса PQ в базу данных PostgreSQL. В файле примере есть запрос PQ_price это данные которые необходимо экспортировать в БД и запрос PSQL_price это таблица в БД в которую нужно импортировать данные из запроса PQ_price. Гугл говорит, что в PQ существуют функции для работы с БД, для которых можно передать свой SQL запрос в качестве аргумента. Например, PostgreSQL.Database и Value.NativeQuery. Но не смог найти использование этих функций именно для экспорта данных. Подскажите возможно ли это?
P.S. Ура! Получилось Файл с решением в первом сообщении. Сначала удаляются данные в таблице из БД с помощью команды TRUNCATE, а затем через INSERT INTO загружаются новые данные из PQ.
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 написал: жаль, что таблицы в SQL все-таки придется создавать вручную.
Какие ваши доказательства? Или у той учётной записи есть права только DML, но не на DDL? А в чём смысл создания произвольной таблицы средствами Power Query? Поделитесь знаниями, интересно же
Потому что без первичного ключа вы можете не один раз вставить одно и тоже. С ним, да, повторная вставка данных для одного и того же значения ключа будет отвергнута, но талантов же у нас много.... Главное напихать и отчитаться
anvg, Эм... у меня 9 таблиц, которые нужно перекинуть в SQL базу, зачем мне для каждой таблицы вручную все дело прописывать, менять названия колонок, если можно использовать универсальную функцию для всех 9 таблиц? Можете научить меня создавать таблицу не вручную, автоматически прописывая тип данных для каждой из 9 таблиц на сервере SQL? В них и тип текст, и дата, и интервал времени, и десятичное число. было бы круто ели Вы создадите функцию которая создает в SQL таблицу на сервере из имеющейся, например используя выше представленный пример.
БД это строго структурированное типизированное и, как правило, индексированное хранилище данных, которое заранее спроектировано для определённых целей, со строго настроенными взаимосвязями между таблицами и ограничениями. Не следует БД рассматривать, как этакий безразмерный Excel. Поэтому просто так создавать в БД таблицу, подобно листу в Excel по существу не имеет смысла. Вы так и не ответили, зачем создавать таблицу? Изучите SQL CREATE TABLE Statement. Только учтите, что как правило каждый движок БД имеет свой диалект. ANSI DDL поддерживается, но на уровне определения типов данных столбцов таблицы, могут быть заметные различия.
anvg написал: Вы так и не ответили, зачем создавать таблицу
Знаете, я бы с удовольствием ответил. но понимаю сути вопроса. Загрузка на сервер нужна сугубо для целей общего пользования, и просто безразмерного хранилища данных. Связи между таблицами пока что, не будут существовать. Мне нужно организовать обмен данными между несколькими пользователями, и хранение объема информации для анализа. Большее пока что не требуется. Кстати для этих целей почти идеально подошла надстройка MySqlforExcel но есть свои минусы, об этом писал в соседней ветке. Просто выгрузка на сервер, поправка небольшая таблиц, не больше. Много пользователей может использовать данные. Каждому пользователю можно выдать привилегии кому только читать таблицу, кому и запись в нее делать. Удобно ведь.
anvg, большой проблемы нет, если есть первичный ключ, и знаете, что делать и т.п. В основном МС не рекомендует использовать PQ для прямой загрузки в БД из-за потенциально многократного обращения PQ к источнику (которым в данном случае является БД) из-за специфики языка. Можно легко напортачить. Хотя в SSAS он, кажется, как раз для этого и используется.
Кстати можно даже проще сделать. Вот тут произвел конвертацию в текст в формате JSON, делается это также с помощью функции getJSON, но она состоит всего из 2х команд. Для дальнейшей загрузки данных в БД PostgreSQL из формата JSON я в запросе SQL_INSERT_INTO использовал json_populate_recordset. К сожалению не знаю какой из 2х вариантов загрузки данных быстрее.
Murderface_, Подскажите пожалуйста, а Вы не знаете ли Как внутри запроса к SQL серверу задать Имя пользователя и пароль вручную? В сроке именно это возможно сделать?
Максим Зеленский написал: Хотя в SSAS он, кажется, как раз для этого и используется.
Ну, да, также как и Power BI и Power Pivot model. Только это не последовательное добавление новых/обновление существующих данных, просто кэширование результата выполнения запроса Power Query.
lostandleft написал: Как внутри запроса к SQL серверу задать Имя пользователя и пароль вручную
Скорее всего никак. У меня подключение к серверу происходит на первом шаге запроса SQL_INSERT_INTO с помощью функции PostgreSQL.Database, которая в качестве агрументов может принимать адрес сервера и имя базы данных. Логин и пароль вводятся при первом подключении к БД, появится кнопка "Изменить учетные данные" куда вводится логин/пароль, Эти данные хранятся локально. Т. е. при запуске запроса на разных компьютерах нужно будет вводить логопасс при первом подключении к БД. Сохраненные логины/пароли хранятся в параметрах источника данных.
Гуглю целый день, похоже на то. У меня тоже самое. Правда коннект к Mysql. Не нравится мне что никак не сделать нормальное сохранение имени пользователя и пароля. К каждому юзеру придется на машину идти покдключаться к БД.
Планировалось конкретному Васе на сервере учетку ограничить, или убить. Это проще чем к нему идти и что-то настраивать. Но если так задумано, будем учиться жить как правильно
anvg написал: Разошлите учётные данные по e-mail - пусть сами и вводят.
Я хотел вместе с файлом сразу же все выслать и учетные данные для подключения, внутри запроса. Чтобы пользователь просто открыл фаил и начал пользовался. Без дополнительных настроек.