CODEWITHSUNDEEP
azure-data-factory
Mike Kiser
Mike Kiser

Reputation: 33

Azure Data Factory Mapping ADFv2

95% of my Mapping in the Copy Activity of ADFv2 is straight one to one mapping. However, I have a case where I need to map Source "MIDDLE_INIT" Column to the Sink Column in which the String must be SUBSTR(PS_NAMES.MIDDLE_NAME,1,1). Can this be done in ADFv2 Copy Activity? (The government cloud doesn't have Data Flows so I have to use the Copy Activity). Thanks Mike

Upvotes: 0

Views: 158

Answers (2)

wBob
wBob

Reputation: 14389

If you have Azure SQL Database in your architecture, you can use it to shred JSON directly from your data lake, using its built-in abilities, namely OPENROWSET and OPENJSON. Here is a simple example:

SELECT *
FROM OPENROWSET (
    BULK 'raw/parliament/2020/09/25/members.json',
    DATA_SOURCE = 'somejsonstore',
    SINGLE_CLOB
) x
CROSS APPLY OPENJSON ( BulkColumn, '$.result.items' )
WITH (
    fullName            NVARCHAR(MAX)       '$.fullName._value',
    gender              NVARCHAR(100)       '$.gender._value',
    party               NVARCHAR(100)       '$.party._value'
)

Read more about OPENJSON here.

Here is another simple example shredding JSON which I imagine is similar to yours. NB in this I create the JSON, but you'll need to import it using the above technique:

SET @json = '{
"PS_NAMES": {
    "FIRST_NAME": "w",
    "MIDDLE_NAME": "Susan",
    "LAST_NAME": "Bob"
    }
}'

;WITH cte AS (
SELECT *
FROM OPENJSON ( @json, '$.PS_NAMES' )
WITH (
    FIRST_NAME          NVARCHAR(100)       '$.FIRST_NAME',
    MIDDLE_NAME         NVARCHAR(100)       '$.MIDDLE_NAME',
    LAST_NAME           NVARCHAR(100)       '$.LAST_NAME'
    )
)
SELECT 'original'AS [source], FIRST_NAME, MIDDLE_NAME, LAST_NAME
FROM cte
UNION ALL
SELECT 'new', FIRST_NAME, LEFT( MIDDLE_NAME, 1 ) AS MIDDLE_INIT, LAST_NAME
FROM cte;

If you do not have an Azure SQL DB in your architecture, then write back with what you do have, eg do you have Databricks, ADLA, Azure Synapse workspace, Azure Functions, Logic Apps, something else?

Upvotes: 1

Steve Johnson
Steve Johnson

Reputation: 8690

No, copy activity cant do this.

As wBob comments, you could copy data from your source to a table type and transform SUBSTR(PS_NAMES.MIDDLE_NAME,1,1) in stored procedure. Then use this table as source, copy to your sink.

Upvotes: 1

Related Questions