AXForum  
Вернуться   AXForum > Microsoft Dynamics AX > DAX Blogs
All
Забыли пароль?
Зарегистрироваться Правила Справка Пользователи Сообщения за день Поиск Все разделы прочитаны

 
 
Опции темы Поиск в этой теме Опции просмотра
Старый 05.09.2024, 06:34   #1  
Blog bot is offline
Blog bot
Участник
 
25,626 / 848 (80) +++++++
Регистрация: 28.10.2006
dynamicsnavax: Fabric - Sample Notebook scripts for MSDyn365FO
Источник: http://dynamicsnavax.blogspot.com/20...ripts-for.html
==============

This will be a fairly straightforward blog post covering different ways of copying data from a shortcut delta table into a delta table created automatically via a notebook.

Select statement with a join

%%sql
SELECT
party.recid AS PartyId
,party.name AS Name
,COALESCE(party.namealias, '') AS ShortName
,COALESCE(postal.countryregionid, '') AS Country
,COALESCE(postal.state, '') AS State
,COALESCE(postal.city, '') AS City
,COALESCE(postal.street, '') AS Street
,COALESCE(postal.zipcode, '') AS PostCode
,COALESCE(phone.locator, '') AS PhoneNumber
,COALESCE(email.locator, '') AS Email
FROM dirpartytable party
LEFT OUTER JOIN logisticspostaladdress postal ON postal.location = party.primaryaddresslocation
AND postal.validto > current_date() -- filters only valid(effective) addresses
LEFT OUTER JOIN logisticselectronicaddress phone ON phone.recid = party.primarycontactphone
LEFT OUTER JOIN logisticselectronicaddress email ON email.recid = party.primarycontactemail


You should see a table result showing below your query.










Create table if not exists

This is a one of copy and will not copy data if the table exists already.
%%sql
CREATE TABLE IF NOT EXISTS fact_dirpartytable
USING DELTA AS
SELECT
party.recid AS PartyId
,party.name AS Name
,COALESCE(party.namealias, '') AS ShortName
,COALESCE(postal.countryregionid, '') AS Country
,COALESCE(postal.state, '') AS State
,COALESCE(postal.city, '') AS City
,COALESCE(postal.street, '') AS Street
,COALESCE(postal.zipcode, '') AS PostCode
,COALESCE(phone.locator, '') AS PhoneNumber
,COALESCE(email.locator, '') AS Email
FROM dirpartytable party
LEFT OUTER JOIN logisticspostaladdress postal ON postal.location = party.primaryaddresslocation
AND postal.validto > current_date() -- filters only valid(effective) addresses
LEFT OUTER JOIN logisticselectronicaddress phone ON phone.recid = party.primarycontactphone
LEFT OUTER JOIN logisticselectronicaddress email ON email.recid = party.primarycontactemail







Create table if not exists - use merge

This does a copy similar to the above but uses a merge to match the records
%%sql

-- Step 1: Create Delta table
CREATE TABLE IF NOT EXISTS fact3_dirpartytable (
PartyId LONG,
Name STRING,
ShortName STRING,
Country STRING,
State STRING,
City STRING,
Street STRING,
PostCode STRING,
PhoneNumber STRING,
Email STRING
) USING delta;

-- Step 2: Create temporary view
CREATE OR REPLACE TEMPORARY VIEW temp_dirpartytable AS
SELECT
party.recid AS PartyId
,party.name AS Name
,COALESCE(party.namealias, '') AS ShortName
,COALESCE(postal.countryregionid, '') AS Country
,COALESCE(postal.state, '') AS State
,COALESCE(postal.city, '') AS City
,COALESCE(postal.street, '') AS Street
,COALESCE(postal.zipcode, '') AS PostCode
,COALESCE(phone.locator, '') AS PhoneNumber
,COALESCE(email.locator, '') AS Email
FROM dirpartytable party
LEFT OUTER JOIN logisticspostaladdress postal ON postal.location = party.primaryaddresslocation
AND postal.validto > current_date() -- filters only valid(effective) addresses
LEFT OUTER JOIN logisticselectronicaddress phone ON phone.recid = party.primarycontactphone
LEFT OUTER JOIN logisticselectronicaddress email ON email.recid = party.primarycontactemail;

-- Step 3: Merge into delta table
MERGE INTO fact3_dirpartytable AS target
USING temp_dirpartytable AS source
ON target.PartyId = source.PartyId
WHEN MATCHED THEN
UPDATE SET target.Name = source.Name, target.ShortName = source.ShortName
WHEN NOT MATCHED THEN
INSERT (PartyId, Name, ShortName) VALUES (source.PartyId, source.Name, source.ShortName);






This will do an update, insert but will not handle deletes.













Create table, Delete and Insert data

This creates the table, then deletes the data in full and inserts it all again.
%%sql

-- Step 1: Create Delta table
CREATE TABLE IF NOT EXISTS fact4_dirpartytable (
PartyId LONG,
Name STRING,
ShortName STRING,
Country STRING,
State STRING,
City STRING,
Street STRING,
PostCode STRING,
PhoneNumber STRING,
Email STRING
) USING delta;

-- Step 2: Delete data from the Delta table
DELETE FROM fact4_dirpartytable;

-- Step 3: Create temporary view
INSERT INTO fact4_dirpartytable
SELECT
party.recid AS PartyId
,party.name AS Name
,COALESCE(party.namealias, '') AS ShortName
,COALESCE(postal.countryregionid, '') AS Country
,COALESCE(postal.state, '') AS State
,COALESCE(postal.city, '') AS City
,COALESCE(postal.street, '') AS Street
,COALESCE(postal.zipcode, '') AS PostCode
,COALESCE(phone.locator, '') AS PhoneNumber
,COALESCE(email.locator, '') AS Email
FROM dirpartytable party
LEFT OUTER JOIN logisticspostaladdress postal ON postal.location = party.primaryaddresslocation
AND postal.validto > current_date() -- filters only valid(effective) addresses
LEFT OUTER JOIN logisticselectronicaddress phone ON phone.recid = party.primarycontactphone
LEFT OUTER JOIN logisticselectronicaddress email ON email.recid = party.primarycontactemail;







This runs fast but however not what you may want to do on a regular basis.














Create a temporary view and use SinkModifiedOn

Create a temporary view within your notebook to use as part of complex queries.
This query joins 4 tables together and each table has its own SinkModifiedOn field. I wanted to create a view that gave me the greatest (max) SingModifiedOn date time. This is to later allow me to do an incremental update.
CREATE OR REPLACE TEMPORARY VIEW temp_dirpartytable AS
SELECT
party.SinkModifiedOn AS party_SinkModifiedOn,
postal.SinkModifiedOn AS postal_SinkModifiedOn,
phone.SinkModifiedOn AS phone_SinkModifiedOn,
email.SinkModifiedOn AS email_SinkModifiedOn,
GREATEST(party.SinkModifiedOn, postal.SinkModifiedOn, phone.SinkModifiedOn, email.SinkModifiedOn) AS SinkModifiedOn,
party.recid AS PartyId
,party.name AS Name
,COALESCE(party.namealias, '') AS SearchName
,COALESCE(postal.countryregionid, '') AS Country
,COALESCE(postal.state, '') AS State
,COALESCE(postal.city, '') AS City
,COALESCE(postal.street, '') AS Street
,COALESCE(postal.zipcode, '') AS PostCode
,COALESCE(phone.locator, '') AS PhoneNumber
,COALESCE(email.locator, '') AS Email
FROM dirpartytable party
LEFT OUTER JOIN logisticspostaladdress postal ON postal.location = party.primaryaddresslocation
AND postal.validto > current_date() -- filters only valid(effective) addresses
LEFT OUTER JOIN logisticselectronicaddress phone ON phone.recid = party.primarycontactphone
LEFT OUTER JOIN logisticselectronicaddress email ON email.recid = party.primarycontactemail;

SELECT * FROM temp_dirpartytable
WHERE SinkModifiedOn >= '2024-09-03T02:39:16Z';









This would be good for a transactional table where there are no deletes. You get the last SinkModifiedOn date time field for all the related table. Then filter based on the last run you have in your destination table. You could then do incremental updates.








Источник: http://dynamicsnavax.blogspot.com/20...ripts-for.html
__________________
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору.
 

Похожие темы
Тема Автор Раздел Ответов Посл. сообщение
dynamicsnavax: Exploring Analytical Options with Dynamics 365 Finance and Operations: Link to Fabric Blog bot DAX Blogs 0 04.09.2024 07:16
emeadaxsupport: Writing Data Upgrade Scripts Part 1: Understanding the components of the process Blog bot DAX Blogs 0 10.02.2012 05:16
Microsoft Dynamics CRM Team Blog: Create Sample Data for your Solution Blog bot Dynamics CRM: Blogs 0 01.12.2011 05:14
Microsoft Dynamics CRM Team Blog: Creating Custom Sample Data for CRM 2011 - Advanced Blog bot Dynamics CRM: Blogs 0 30.04.2011 01:13
Microsoft Dynamics CRM Team Blog: Creating Custom Sample Data for Microsoft Dynamics CRM 2011 Blog bot Dynamics CRM: Blogs 0 07.01.2011 03:15
Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск
Опции просмотра

Ваши права в разделе
Вы не можете создавать новые темы
Вы не можете отвечать в темах
Вы не можете прикреплять вложения
Вы не можете редактировать свои сообщения

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.
Быстрый переход

Рейтинг@Mail.ru
Часовой пояс GMT +3, время: 23:17.