Work with decimal data

This page explains how to convert fields into the decimals and perform transformations on them when you prepare data in the Wrangler workspace of the Cloud Data Fusion Studio.

Read decimal data

1. Go to the Wrangler workspace in Cloud Data Fusion.

2. Open an object, such as a table, from a database or a Cloud Storage file.

   • For a database or a BigQuery connection, if the table has a decimal column, Wrangler converts it into a BigDecimal type. When you create the pipeline from Wrangler, the column is then converted to the decimal data type.

   • If your dataset contains non-decimal data that you want to convert to decimals, use the set-column directive:

     set-column : DECIMAL_COLUMN exp:{new("java.math.BigDecimal", INPUT_COLUMN)}
     

     Replace the following:

     • DECIMAL_COLUMN: the decimal column to be transformed. After the directive executes, the column's data type changes to BigDecimal, and the schema also contains the appropriate data type.

     • INPUT_COLUMN: the column that gets converted, which can be one of the following types: STRING, INTEGER, LONG, FLOAT, or DOUBLE.

   • If your dataset includes values with varying scale, such as 1.05, 2.698, 5.8745512, set the scale with a Wrangler directive and edit the schema in the pipeline to set the scale for the decimal column.

     To set the scale in the Wrangler, use a directive similar to the following:

     set-column : OUTPUT_COLUMN exp:{new("java.math.BigDecimal", DECIMAL_COLUMN).setScale()}
     

     The following example converts a column called cost from a string to a decimal, sets a scale of 9, and outputs the results to a new column called output-column:

     set-column : output-column exp:{new("java.math.BigDecimal", "cost").setScale(9)}
     

Transform decimal data

Decimal columns in Wrangler use the Java BigDecimal class. After the columns are converted to the BigDecimal data type, transform the columns with methods from Class BigDecimal.

Replace the following:

• OUTPUT_COLUMN: the column containing the output of the operation.
• DECIMAL_COLUMN: the decimal column that's transformed.
• DECIMAL_COLUMN_2: the second decimal column included in the operation, such as when you add the values from two decimal columns together.

What's next

• Learn more about Wrangler directives.