Join this group to access featured content and participate in discussions related to DMX.

140 Members
Join Us!

I am new to SyncSort. Our goal is to drop data from Oracle table under different schemas as hdfs file in created file structure.

source: schema1.movement union schema2.movement ... schema15.movement ...etc (could have new schema added in the future, so cannot be hard coded)

target: Hoping SyncSort will use the different value of schema to create sub folder dynamically for partition under t_movement folder that has been created in hdfs 

t_movement folder

     schema1 sub-folder

     schema2 sub-folder

....

     schema15 sub-folder

Question is can SyncSort do below?

(1) Dynamic SQL for specifying source - When specify "add source", if SyncSort can execute a dynamic SQL so schema name won't be hard coded (in Oracle PL SQL we can use EXECUTE IMMEDIATELY for dynamic SQL) or execute a PL/SQL at run time?

(2) Dynamic Partition - Dynamically create the partition to the hdfs file definition from source of Oralce?

Thanks

Fiona

You need to be a member of Syncsort Community to add comments!

Join Syncsort Community

Email me when people reply –

Replies

  • Hi Fiona,

    In regard to your first question, When specifying "add source", if SyncSort can execute a dynamic SQL so schema name won't be hard coded (in Oracle PL SQL we can use EXECUTE IMMEDIATELY for dynamic SQL) or execute a PL/SQL at run time?, this can be done via a user-defined SQL source. You can make it dynamic by using environment variables. Your second question, (2) Dynamic Partition - Dynamically create the partition to the hdfs file definition from source of Oracle?, can be handled by a partition scheme, hive partition, or by parameterizing the target path. However, for your use case, the best solution and more direct way to address both of your questions is to use DataFunnel. DMExpress DataFunnel is a command-line tool that can rapidly transport large volumes of data from relational databases to a variety of different data stores in a single command. It uses a simple JSON configuration file to define the set of data transfers to be executed.

    I think a good starting point for you is to read up on the topic of DMExpress Data Funnel in DMExpress Help.  

    Kind regards,

    Jennifer

This reply was deleted.

To access Syncsort Knowledge Base, visit:

My Support