hide's memo
28 Apr, 2022

How to use PostgreSQL as a sink (Azure Data Factory)

[日本語]

This is sample of how to use PostgreSQL as a sink.

ADF can’t use PostgreSQL as a sink without using Self-hosted integration runtime.

So, at first, you need to set up a self-hosted runtime.
(See What is the Self Hosted Runtime(Azure Data Factory))

There is no PostgreSQL link service which can be used as a sink.
so you need to set “ODBC” link service.
You need to create the environment as below.

 

 

■Step1

Install PostgreSQL ODBC Driver as follows.

(1)Download PostgreSQL ODBC Driver
(1.1)access https://www.postgresql.org/
(1.2)”Download” -> “File Browser” -> “odbc” -> “versions” -> “msi”
(1.3)Download psqlodbc_13_02_0000-x64.zip (at first, I tried Version 9.2. but it didn’t work)

(2)Install ODBC Driver

 

■Step2

(1)Set sink’s dataset “ODBC”
(2)Set ODBC’s linked service as below.

19 Feb, 2022

Using WebAccess activities response body to next WebAccess activities request body(Azure Data Factory)

[japanese(日本語)]

Using WebAccess activities response body to next WebAccess activities request body(Azure Data Factory)

[Alert] Web Access activity  can’t  handle more than 4MB. [Microsoft’s web site]

Web2 Settings

Method：POST

Body： @concat(‘{ “records”:’, activity(‘Web1’).output.records, ‘}’)

Thre result of Web1 Activity is not a simple strings. You can get value by using “activity(‘Web1’).output.records”.

But it is an array object and ommited the first

{
     "records" : [

And the last

   ]
}

So. You can build string by using concat function.

 

 

18 Feb, 2022

convert json file without using Copy Activity (Azure Data Factory)

japanese(日本語)

 

Azure Data Factory.

How to convert json file format without using Copy Activity.

Basically, You shuld use Copy Activity to convert json. But If you want to do something detailed, you can manage it by using Lookup, ForEach, SetVariable Activities.

In this case, You should manage to escape strings such liken “\n”, “\r”, “\”….

1.json file on BLOB storage

{
     "datarecords" : [
          {  "name" : "name01",  "value" : " value01" },
          {  "name" : "name02",  "value" :  "value02" }
    ]
}

2.converted json

{
     "records" : [
          { 
　　　　　　　　　　　　　   "attr" : { "rid" : "name01"},
　　　　　　　　　　　　　　　　"id" : "name01", 
                 "info" : "value01"
　　　　　　　　　　},
          { 
                "attr" : { "rid" : "name01"},
　　　　　　　　　　　　　　　"id" : "name02",
                "info" : "value02"
　　　　　　　　　　}
     ]
}

 

3. Pipeline

3.1. Pileline variables

BUFFER_V1_1   String
BUFFER_V1_2   String
BUFFER_V1_3   String

3.2. Pipeline structure

3.3. Lookup1 

  • SRC …. specify  json file

  • Uncheck “First row only”

 

3.4. ForEach1 

  • Check “Sequential”

  • Items：
    @activity('Lookup1').output.value[0].datarecords

 

3.5. Set V1 

  • Name：BUFFER_V1_1

  • Value： 
    @concat(variable('BUFFER_V1_2'), '{　"attr":{ "rid": "', ESCAPE(item().name), '"},　"id":"', ESCAPE(item().name), '","info":"', ESCAPE(item().value),'"}')

ESCAPE() is omitted. you shuld write below code.

uriComponentToString(replace(replace(replace(replace(replace(urlComponent(coalesce( *** , ”)),’%5C’,’%5C%5C’), ‘%22′,’%5C%22’), ‘%0D’,’%5Cr’), ‘%0A’, ‘%5Cn’), ‘%09’, ‘%5Ct’))

replace(**, ‘%5C’,’%5C%5C) …..  replace “\” to “\\”

replace(**, ‘%22’,’%5C%22) ….. replace “”” to “\””

replace(‘**’, ‘%0D’,’%5Cr’) ….  replace \r to “\r” 

replace(‘**’, ‘%0A’,’%5Cn’) …. replace \n to  “\n”

replace(‘**’, ‘%09′,’%5Ct’) ….. replace \t  to “\t”

3.6. Set V2 

  • Name：BUFFER_V1_2
  • Value： 
    @concat(variable('BUFFER_V1_1'), ',')

 

3.7. Set V3 

  • Name：BUFFER_V1_3
  • Value： 
    @concat(variable('BUFFER_V1_3'), '{ "records" [', variable('BUFFER_V1_1'),'}')

 

。

14 Feb, 2022

convert csv file to json file without using Copy Activity (Azure Data Factory)

[japanese(日本語)]

convert csv file to json file.

How to convert json file format without using Copy Activity.

Basically, You shuld use Copy Activity to convert json. But If you want to do something detailed, you can manage it by using Lookup, ForEach, SetVariable Activities.

In this case, You should manage to escape strings such liken “\n”, “\r”, “\”….

1.csv file on blob storage


"name01","value01"
"name02","value02"

2.json file after converted

{
     "records" : [
          { "id" : "name01", "info" : "value01"},
          { "id" : "name02", "info" : "value02"}
     ]
}

 

3. pileline

3.1. pileline variables

BUFFER_V1_1   String
BUFFER_V1_2   String
BUFFER_V1_3   String

3.2. pileline structure

3.3. Lookup1 (Lookup)

  • SRC …. set src csv file

  • Uncheck “First row only”

 

3.4. ForEach1 (ForEach)

  • Check “sequential”

  • Items(Dynamic content)：
    @activity('Lookup1').output.value

 

3.5. Set V1 (Set variable)

  • Name：BUFFER_V1_1

  • Value(Dynamic content)： 
    @concat(variable('BUFFER_V1_2'), '{"id":"', ESCAPE(item().Prop_0), '","info":"', ESCAPE(item().Prop_1),'"}')


ESCAPE() is omitted. you shuld write below code.

uriComponentToString(replace(replace(replace(replace(replace(urlComponent(coalesce( *** , ”)),’%5C’,’%5C%5C’), ‘%22′,’%5C%22’), ‘%0D’,’%5Cr’), ‘%0A’, ‘%5Cn’), ‘%09’, ‘%5Ct’))

replace(**, ‘%5C’,’%5C%5C) …..  replace “\” to “\\”

replace(**, ‘%22’,’%5C%22) ….. replace “”” to “\””

replace(‘**’, ‘%0D’,’%5Cr’) ….  replace \r to “\r” 

replace(‘**’, ‘%0A’,’%5Cn’) …. replace \n to  “\n”

replace(‘**’, ‘%09′,’%5Ct’) ….. replace \t  to “\t”

 

 

3.6. Set V2 (Set variable)

  • Name：BUFFER_V1_2
  • Value(Dynamic content)： 
    @concat(variable('BUFFER_V1_1'), ',')

 

3.7. Set V3 (Set variable)

  • Name：BUFFER_V1_3
  • Value(Dynamic content)：

    @concat(variable(‘BUFFER_V1_3’), ‘{ “records” [‘, variable(‘BUFFER_V1_1′),’}’)



■Related article

convert csv to json with using Copy Activity(Azure Data Factory)