nifi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Koji Kawamura <ijokaruma...@gmail.com>
Subject Re: Ingest data into SQL database using NiFi
Date Mon, 25 Sep 2017 01:17:48 GMT
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 <js.tianluzhu@gmail.com> 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/

Mime
View raw message