search

keedio / flume-ng-sql-source

Flume Source to import data from SQL Databases
Apache License 2.0
264 stars 164 forks source link

Oracle database Invalid character #19

Closed Deepak-Vohra closed 7 years ago

Deepak-Vohra commented 9 years ago

Seems to be an error in the SQL syntax. The ';' should not be added at end of query. Oracle database generates error with ';' at SQL end.

(PollableSourceRunner-SQLSource-OrclSource) [INFO - org.apache.flume.source.SQLSource.process(SQLSource.java:100)] Query: SELECT * FROM wlslog WHERE id>-1 ORDER BY id; 2015-09-16 11:07:02,896 (PollableSourceRunner-SQLSource-OrclSource) [ERROR - org.apache.flume.source.SQLSource.process(SQLSource.java:157)] SQL exception, check if query for source OrclSource is correctly constructed java.sql.SQLSyntaxErrorException: ORA-00911: invalid character

mvalleavila commented 8 years ago

Hello Deepak,

Sorry for the delay, we we will see this issue and study a release with a hotfix in the next days.

Regards

Deepak-Vohra commented 8 years ago

Thanks for the update. The fix should not modify the SQL syntax for MySQL as for MySQL an error is not generated.

mvalleavila commented 8 years ago

Hello Depak I'm trying to reproduce the behaviour but the source with oracle seems to work fine. Can you show us the properties used in your tests?

thanks

Deepak-Vohra commented 8 years ago

flume.conf for Oracle Database to HDFS. agent1.channels.ch1.type = memory agent1.sources.sql-source.channels = ch1 agent1.channels = ch1 agent1.sinks = HDFS

agent1.sources = sql-source agent1.sources.sql-source.type = org.apache.flume.source.SQLSource

URL to connect to database

agent1.sources.sql-source.connection.url = jdbc:oracle:thin:@127.0.0.1:1521:ORCL

Database connection properties

agent1.sources.sql-source.user = OE
agent1.sources.sql-source.password = OE
agent1.sources.sql-source.table = WLSLOG agent1.sources.sql-source.database = ORCL

agent1.sources.sql-source.columns.to.select = *

Increment column properties

agent1.sources.sql-source.incremental.column.name = id

Increment value is from you want to start taking data from tables (0 will import entire table)

agent1.sources.sql-source.incremental.value = 0

Query delay, each configured milisecond the query will be sent

agent1.sources.sql-source.run.query.delay=10000

Status file is used to save last readed row

agent1.sources.sql-source.status.file.path = /var/lib/flume agent1.sources.sql-source.status.file.name = sql-source.status

agent1.sinks.HDFS.channel = ch1 agent1.sinks.HDFS.type = hdfs agent1.sinks.HDFS.hdfs.path = hdfs://10.0.2.15:8020/flume/oradb agent1.sinks.HDFS.hdfs.file.Type = DataStream

Deepak-Vohra commented 8 years ago

The issue is fixed, may close the issue.

The error was due to wrong class name in flume.conf.

agent1.sources.sql-source.type = org.apache.flume.source.SQLSource

Should be:

agent1.sources.sql-source.type = org.keedio.flume.source.SQLSource