Ingest data into SQL database using NiFi

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

Ingest data into SQL database using NiFi

tzhu
Hi everyone,

I'm quite new to this. I want to extract some data from one table, do some
modification, and put it back to another table in SQL server by NiFi. I have
no problem using ExecuteSQL. Then I use ConvertAvroToJSON and
ConvertJSONToSQL to put the data back to database. I would have the
following error message from ConvertJSONToSQL:

<http://apache-nifi-developer-list.39713.n7.nabble.com/file/t792/NiFi.png>

I tried to do the SQL query and it works fine. The following is my SQL
query:

insert into [TEST].[db_datawriter].[Test1]
SELECT TOP (1000) [DATE_INSERT] as [Date]
  ,[G_INSTITUTION_ID] as [Institution]
  FROM [AUTHS_TRXS].[dbo].[TRANSACTIONS]

Does anyone know how I should fix this or any other method I should try? Any
advice is appreciated.

Thanks,

Tina



--
Sent from: http://apache-nifi-developer-list.39713.n7.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: Ingest data into SQL database using NiFi

Koji Kawamura-2
Hello Tina,

The ConvertJSONToSQL error message indicates that the input JSON
object has a field named 'Date', but the target table does not have
such column.
ConvertJSONToSQL maps JSON fields to columns with the same name.
If your destination table does not have a column named 'Date', then
you will need either:

1. Use JoltTransformJSON processor to convert 'Date' field name to the
name of the target column
2. Modify the query SQL 'as [Date]' to appropriate one, so that the
result column name matches with the destination column name

> insert into [TEST].[db_datawriter].[Test1]
> SELECT TOP (1000) [DATE_INSERT] as [Date] (<-- Modify here)
>   ,[G_INSTITUTION_ID] as [Institution]
>   FROM [AUTHS_TRXS].[dbo].[TRANSACTIONS]

I hope this helps.

Thanks,
Koji

On Sat, Sep 23, 2017 at 5:10 AM, tzhu <[hidden email]> wrote:

> Hi everyone,
>
> I'm quite new to this. I want to extract some data from one table, do some
> modification, and put it back to another table in SQL server by NiFi. I have
> no problem using ExecuteSQL. Then I use ConvertAvroToJSON and
> ConvertJSONToSQL to put the data back to database. I would have the
> following error message from ConvertJSONToSQL:
>
> <http://apache-nifi-developer-list.39713.n7.nabble.com/file/t792/NiFi.png>
>
> I tried to do the SQL query and it works fine. The following is my SQL
> query:
>
> insert into [TEST].[db_datawriter].[Test1]
> SELECT TOP (1000) [DATE_INSERT] as [Date]
>   ,[G_INSTITUTION_ID] as [Institution]
>   FROM [AUTHS_TRXS].[dbo].[TRANSACTIONS]
>
> Does anyone know how I should fix this or any other method I should try? Any
> advice is appreciated.
>
> Thanks,
>
> Tina
>
>
>
> --
> Sent from: http://apache-nifi-developer-list.39713.n7.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: Ingest data into SQL database using NiFi

tzhu
Hi Koji,

The "transaction" table only has two columns, "Date" and "Institution". I
don't know what else I could change to...

Thanks,
Tina



--
Sent from: http://apache-nifi-developer-list.39713.n7.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: Ingest data into SQL database using NiFi

Koji Kawamura-2
Hi Tina,

I wonder if the column name is the cause of that issue, because 'date'
is a reserved keyword.
I wonder whether ConvertJSONToSQL can wrap those columns with square
brackets as shown in your example query.

If possible, can you try to change the column name to different one
such as 'tx_date' and see if it works?

Thanks,
Koji

On Mon, Sep 25, 2017 at 10:13 PM, tzhu <[hidden email]> wrote:

> Hi Koji,
>
> The "transaction" table only has two columns, "Date" and "Institution". I
> don't know what else I could change to...
>
> Thanks,
> Tina
>
>
>
> --
> Sent from: http://apache-nifi-developer-list.39713.n7.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: Ingest data into SQL database using NiFi

Koji Kawamura-2
The list of reserved keywords:
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/reserved-keywords-transact-sql

On Tue, Sep 26, 2017 at 9:31 AM, Koji Kawamura <[hidden email]> wrote:

> Hi Tina,
>
> I wonder if the column name is the cause of that issue, because 'date'
> is a reserved keyword.
> I wonder whether ConvertJSONToSQL can wrap those columns with square
> brackets as shown in your example query.
>
> If possible, can you try to change the column name to different one
> such as 'tx_date' and see if it works?
>
> Thanks,
> Koji
>
> On Mon, Sep 25, 2017 at 10:13 PM, tzhu <[hidden email]> wrote:
>> Hi Koji,
>>
>> The "transaction" table only has two columns, "Date" and "Institution". I
>> don't know what else I could change to...
>>
>> Thanks,
>> Tina
>>
>>
>>
>> --
>> Sent from: http://apache-nifi-developer-list.39713.n7.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: Ingest data into SQL database using NiFi

tzhu
Hi Koji,

It shows the same error as I change "date" to "tx_date". You can see the
error message in the image attached.

<http://apache-nifi-developer-list.39713.n7.nabble.com/file/t792/nifi_error.png>

In the meantime, I'm trying to use ExecuteSQL, ReplaceText, PutSQL to do the
same thing. While it doesn't throw an error when processing, the value does
not get copied from the source table but only default format of the datatype
(showing "1900-01-01" for date and "0" for int).

My original idea was to combine the templates " Database Extract with NiFi
<https://www.batchiq.com/database-extract-with-nifi.html>  " and " Database
Ingest with NiFi <https://www.batchiq.com/database-injest-with-nifi.html>  "
so that NiFi selects the data from one table and insert into another one.
Now I'm thinking maybe the idea was wrong. If you know some better way to
solve this problem, please let me know.

Thanks,

Tina



--
Sent from: http://apache-nifi-developer-list.39713.n7.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: Ingest data into SQL database using NiFi

Koji Kawamura-2
Hi Tina,

I tested ExecuteSQL -> ConvertAvroToJSON -> ConvertJSONToSQL -> PutSQL
flow with my SQL Server.
It worked fine, I was able to copy rows from a table to another.
One thing to note is that since you're using two different databases,
you need to specify 'Catalog Name' and 'Schema Name' at
ConvertJSONToSQL properly.

I've written a summary at this Gist page, with SQL examples,
screenshot and flow template:
https://gist.github.com/ijokarumawak/42c257afb5e80361e502564085d7999e

I hope you can find it useful.

Thanks,
Koji

On Wed, Sep 27, 2017 at 4:59 AM, tzhu <[hidden email]> wrote:

> Hi Koji,
>
> It shows the same error as I change "date" to "tx_date". You can see the
> error message in the image attached.
>
> <http://apache-nifi-developer-list.39713.n7.nabble.com/file/t792/nifi_error.png>
>
> In the meantime, I'm trying to use ExecuteSQL, ReplaceText, PutSQL to do the
> same thing. While it doesn't throw an error when processing, the value does
> not get copied from the source table but only default format of the datatype
> (showing "1900-01-01" for date and "0" for int).
>
> My original idea was to combine the templates " Database Extract with NiFi
> <https://www.batchiq.com/database-extract-with-nifi.html>  " and " Database
> Ingest with NiFi <https://www.batchiq.com/database-injest-with-nifi.html>  "
> so that NiFi selects the data from one table and insert into another one.
> Now I'm thinking maybe the idea was wrong. If you know some better way to
> solve this problem, please let me know.
>
> Thanks,
>
> Tina
>
>
>
> --
> Sent from: http://apache-nifi-developer-list.39713.n7.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: Ingest data into SQL database using NiFi

tzhu
Hi Koji,

Thank you so much for your help! I didn't specify the 'Catalog Name' and
'Schema Name' before, and now the error is fixed.

Now I have another question: After getting converted into different
datatype, the data size gets very large. The read size in ExecuteSQL is
about 200 MB, and the size in ConvertJSONToSQL becomes 1 GB. Is there any
way to reduce the size? I'm thinking about two solutions. One is to use
other efficient processors; or to split the input into small pieces, and
maybe take 1000 rows every time to do the transformation.

Hope this makes sense to you.

Thank you,

Tina



--
Sent from: http://apache-nifi-developer-list.39713.n7.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: Ingest data into SQL database using NiFi

Koji Kawamura-2
Hi Tina,

Glad to hear you were able to get schema.

The read size in ExecuteSQL is less because it's serialized with Avro
in which data can be written efficiently, and gets bigger after
ConvertJSONToSQL because each FlowFile has SQL statement in it.

Which version of Apache NiFi are you using? If you can use 1.3.0, I'd
recommend to use QueryRecord to transform data, and PutDatabaseRecord
to store rows into the destination table.

If you're not familiar with Record data model, Mark's blog would be helpful:
https://blogs.apache.org/nifi/entry/record-oriented-data-with-nifi

Once you know how Record works, you can do interesting things such as
transform data using SQL against FlowFile:
https://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi/nifi-standard-nar/1.3.0/org.apache.nifi.processors.standard.QueryRecord/index.html

With record data model, you don't have to split each row nor convert
to SQL statement one by one. Instead a FlowFile containing multiple
records (rows) can be passed around processors to be processed more
efficiently.

Thanks,
Koji


On Thu, Sep 28, 2017 at 4:48 AM, tzhu <[hidden email]> wrote:

> Hi Koji,
>
> Thank you so much for your help! I didn't specify the 'Catalog Name' and
> 'Schema Name' before, and now the error is fixed.
>
> Now I have another question: After getting converted into different
> datatype, the data size gets very large. The read size in ExecuteSQL is
> about 200 MB, and the size in ConvertJSONToSQL becomes 1 GB. Is there any
> way to reduce the size? I'm thinking about two solutions. One is to use
> other efficient processors; or to split the input into small pieces, and
> maybe take 1000 rows every time to do the transformation.
>
> Hope this makes sense to you.
>
> Thank you,
>
> Tina
>
>
>
> --
> Sent from: http://apache-nifi-developer-list.39713.n7.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: Ingest data into SQL database using NiFi

tzhu
Hi Koji,

I was not working on NiFi for a while, and now I'm back with more
questions...

In QueryRecord, how should I write a SQL query? It doesn't seem to give me
the choice to use SQL anywhere, even if I choose the script option. And what
exactly should I do for the reader and writer?

Thanks,

Tina



--
Sent from: http://apache-nifi-developer-list.39713.n7.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: Ingest data into SQL database using NiFi

Pierre Villard
Hi Tina,

For QueryRecord processor, I'd recommend you reading this excellent article
[1].

[1] https://blogs.apache.org/nifi/entry/real-time-sql-on-event

Hope this helps,
Pierre

2017-11-10 21:59 GMT+01:00 tzhu <[hidden email]>:

> Hi Koji,
>
> I was not working on NiFi for a while, and now I'm back with more
> questions...
>
> In QueryRecord, how should I write a SQL query? It doesn't seem to give me
> the choice to use SQL anywhere, even if I choose the script option. And
> what
> exactly should I do for the reader and writer?
>
> Thanks,
>
> Tina
>
>
>
> --
> Sent from: http://apache-nifi-developer-list.39713.n7.nabble.com/
>