Pentaho Spoon and cpimport

6 posts / 0 new
Last post
davestokes
davestokes's picture
Offline
Last seen: 3 years 3 months ago
Joined: Apr 24 2010
Gold Boarder

Posts: 116

David Stokes
Pentaho Spoon and cpimport

The [i]cpimport[/i] program is the fastest way to load data into a InfiniDB instance. It can be used with Pentaho's spoon as the last step in a transformation.

1. Use [i]colxml[/i] to set up the [i]cpimport[/i] job, colxml [u]-j 123[/u] -l data.tbl -t demo test
2. Set up the Spoon transformation so that the data file ends up in /usr/local/Calpont/data/bulk/data/import/ where [i]cpimport[/i] expects to find it.
3. Drag a [b]Text file output[/b] icon onto the transformation panel and link it as the last hop in the transformation.
4. Double click on the icon. Set the Filename to [i]/usr/local/Calpont/bin/cpimport -j 123[/i] and select [i]'Run this as a command instead?'[/i]
5. Run the transformation.

A more detailed document on using Spoon with InfiniDB is in the works.

j__c__
j__c__'s picture
Offline
Last seen: 7 months 4 days ago
Joined: Feb 7 2010
Senior Boarder

Posts: 25

J C
Re:Pentaho Spoon and cpimport

I have created a simple script to do the import from Spoon using the STDIN attribute of cpimport and Text file output step. I am also using carte in the infinidb machine with root privileges in order to provide the data from Spoon without the neccesity to put the file in /usr/local/Calpont/data/bulk/data/import/ before importing.
The cpimport.sh that I use is:
[code]
#!/bin/bash
echo "Use:"
echo "cpimport.sh schema table job"
echo "cpimport.sh test_schema test_table 100"

/usr/local/Calpont/bin/colxml $1 -t $2 -j $3

/usr/local/Calpont/bin/cpimport -f STDIN -j $3
[/code]

To call it from Spoon, in the Text fiel output step in filename field I put:

${path_to_cpimport}/cpimport.sh schema dim_table 108

In such way I import data directly from the transformation without putting the file first in /usr/local/Calpont/data/bulk/data/import/.

I hope this can help someone else.

Juan Carlos

davestokes
davestokes's picture
Offline
Last seen: 3 years 3 months ago
Joined: Apr 24 2010
Gold Boarder

Posts: 116

David Stokes
Re:Pentaho Spoon and cpimport

Thanks Juan Carlos!

sanjv
sanjv's picture
Offline
Last seen: 1 day 12 hours ago
Joined: Mar 10 2010
Junior Boarder

Posts: 10

Sanjay Vekaria
Re:Pentaho Spoon and cpimport

Did this guide get produced? We're just starting with Spoon and this could be useful.

davidhill
davidhill's picture
Offline
Last seen: 1 month 2 weeks ago
Joined: Oct 27 2009
Administrator

Posts: 595

david hill
Re:Pentaho Spoon and cpimport

Spoon and some other BI Quick-Start guides can be found here:

[url=http://www.calpont.com/resources/bi-quickstarts]http://www.calpont.com/r...

David H
Calpont

sanjv
sanjv's picture
Offline
Last seen: 1 day 12 hours ago
Joined: Mar 10 2010
Junior Boarder

Posts: 10

Sanjay Vekaria
Re: Pentaho Spoon and cpimport

hi - if i have infiniDB on a linux machine, MUST spoon be installed on the same machine to use the example in your guide?

I've been wondering if there is any way to use spoon on my local windows machine and still have it upload and process the files to my linux infinidb tables.