hide's memo
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

how to create self-signed certificate

[japanese(日本語)]

 

1.how to create self-signed certificate

!/bin/sh

CN=******.co.jp
PASSWORD=abcdefgxyz
IP=127.0.0.1

echo subjectAltName = DNS:$CN, IP:$IP > subjectname.txt

SJ="/C=JP/ST=Tokyo/L=Minato-ku/O=****/OU=****/CN=$CN"

openssl genrsa -des3 -passout pass:${PASSWORD} -out ${CN}.key 2048

openssl rsa -passin pass:${PASSWORD} -in ${CN}.key -out ${CN}.key

openssl req -new -sha256 -key ${CN}.key -out ${CN}.csr -subj "$SJ"

#openssl req -noout -text -in ${CN}.csr

#openssl req -x509 -in ${CN}.csr -key ${CN}.key -out ${CN}.crt -days 3650
openssl x509 -days 3650 -req -extfile subjectname.txt -signkey  ${CN}.key < $CN.csr > $CN.crt

#CHANGE CSR to PFX
openssl pkcs12 -export -in ${CN}.crt -inkey ${CN}.key -out ${CN}.pfx -passout pass:${PASSWORD}

 

 

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)