Ask Your Question
1

Use salesforce scale and precision field attributes with Hive

asked 2018-05-16 09:32:13 -0500

tclaw46 gravatar image

updated 2018-05-16 10:52:45 -0500

I am using the salesforce origin to extract data and transfer them to Hive. The data has a lot of decimal data type values and I want the scale and precision to be dynamic for each value so I don't end up with values that have a lot of 0's on the right side of the period. Is there a way to set the scale and precision rows in the Hive metadata stage to use the salesforce field attributes for each value?

edit retag flag offensive close merge delete

Comments

1

To clarify - you want different scale/precision for each column in Hive, instead of all the columns in the table using the same scale/precision?

metadaddy gravatar imagemetadaddy ( 2018-05-16 12:23:47 -0500 )edit

Yes that is correct.

tclaw46 gravatar imagetclaw46 ( 2018-05-16 14:11:10 -0500 )edit

1 Answer

Sort by ยป oldest newest most voted
0

answered 2018-05-18 14:26:41 -0500

metadaddy gravatar image

As the Hive Metadata Processor documentation mentions, you can use the following default expressions when processing data from the JDBC Multitable Consumer or the JDBC Query Consumer origins:

${record:attribute(str:concat(str:concat('jdbc.', field:field()), '.precision'))}
${record:attribute(str:concat(str:concat('jdbc.', field:field()), '.scale'))}

Now, the above expressions reference record header attributes, but the Salesforce origin sets the salesforce.precision and salesforce.scale field attributes, so we need to rework the expressions thus:

${record:fieldAttribute(str:concat('/', field:field()), 'salesforce.precision')}
${record:fieldAttribute(str:concat('/', field:field()), 'salesforce.scale')}

Now the Hive columns will be created to match the Salesforce data.

If you want to set the precision/scale explicitly, you can use the Expression Evaluator processor to set a field or record header attribute, and use a similar expression to reference it.

edit flag offensive delete link more

Comments

So it seems to be reading the scale and precision just fine now, but I got this error while running it: "com.streamsets.pipeline.api.base.OnRecordErrorException: HIVE_26 - Invalid decimal value 0.0 in field Primary_Accounts__c: scale 1 is more then expected 0"

tclaw46 gravatar imagetclaw46 ( 2018-05-18 15:13:42 -0500 )edit

Here is the field info: Primary_Accounts__c : DECIMAL 2.0 salesforce.salesforceType:double salesforce.scale:0 salesforce.precision:6 It got the scale of 0 correctly, but for some reason streamsets thinks it should be 1.

tclaw46 gravatar imagetclaw46 ( 2018-05-18 15:14:26 -0500 )edit
metadaddy gravatar imagemetadaddy ( 2018-05-19 11:17:39 -0500 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-05-16 09:32:13 -0500

Seen: 55 times

Last updated: May 18