CODEWITHSUNDEEP
apache-nifisqooptablecolumnkylo
Aparajitha
Aparajitha

Reputation: 31

Import Sqoop column names issue

I have a question on Kylo and Nifi. The version of Kylo used is 0.10.1 The version of Nifi used is 1.6.0

When we create a feed for database ingest (using database as source), in the Additional Options step there is no provision to enter the source table column names.

However, in Nifi side, we use an Import Sqoop processor which has a mandatory field called Source Fields and it requires that the columns be entered, separated by commas. If it is not done, we get an error:

ERROR tool.ImportTool: Imported Failed: We found column without column name. Please verify that you've entered all column names in your query if using free form query import (consider adding clause AS if you're using column transformation)

For our requirement, we want Import Sqoop to take all the columns from the table automatically into this property without manual intervention at Nifi level. Is there any option to include all columns of a database table in the background automatically? Or is there any other possibility of giving this value in UpdateAttribute processor?

Upvotes: 3

Views: 351

Answers (2)

Igor N.
Igor N.

Reputation: 434

Yes, Teradata Kylo Import Sqoop is not standard NiFi processor, but it's there for us to use. Looking deeper at processor's properties, we can see that indeed, SOURCE_TABLE_FIELDS is required there. Then you have an option to manually hard-code the list of columns or set up a method to generate the list dynamically.

Typical solution is to provide the list of fields is by querying table's metadata. A particular solution depends on where source and target tables are set up and how mapping is defined between source and target columns. For example, one could use databases' INFORMATION_SCHEMA tables and match columns by name. Because SQOOP's output should match the source, one has to find a way to generate the column list and provide it to ImportSqoop processor. A better yet approach could involve a separate metadata that would store the source and target information along with mappings and possible transforms (many tools are available there for that purpose, for example, Wherescape).

More specifically, I would use LookupAttribute paired with database or scripted lookup service to retrieve the column list from some metadata provider.

Upvotes: 0

Dennis Jaheruddin
Dennis Jaheruddin

Reputation: 21563

As mentioned in the Comments, ImportSqoop is not a not a normal Nifi processor. This does not have to be problem, but will mean it is probably not possible to troubleshoot the problem without involving the creator.

Also, though I am still debating whether Nifi on Sqoop is an antipattern, it is certainly not necessary. Please look into the standard options first:

  1. Standard way to get data into Nifi from tables is with standard processors such as ExecuteSQL
  2. If that doesn't suffice, the standard way to use Sqoop (a batch tool) is with a batch scheduler, such as Oozie or Airflow

This thread may take away further doubts on point 1: http://apache-nifi.1125220.n5.nabble.com/Sqoop-Support-in-NIFI-td5653.html

Upvotes: 0

Related Questions