BigQuery infinite loop uploading 195x3 CSV file (3rd try)

[yanay]

Hi, learning BigQuery > I upload a CSV file with 195 rows of 3 short columns. Data looks like this:

AFG Afghanistan 2328.76
ALB Albania 13394.46
DZA Algeria 10574.95

For the amount field, if I set up the column as Numeric with precision = 2 (no scale), the upload never completes (spinning wheel for 2 hours). If I set it to Float -- no problem. Any insight?

[ms4446 · Accepted solution]

The issue you're experiencing is likely due to the way BigQuery handles the Numeric data type. The Numeric data type in BigQuery is an exact numeric data type that holds up to 38 digits of precision and 9 digits of scale. This means it can represent very large numbers with a high degree of accuracy, but it also means it requires more computational resources to process.

When you specify a Numeric data type with a precision of 2 and no scale, you're essentially telling BigQuery to expect a number with 2 digits before the decimal point and no digits after the decimal point. However, your data contains numbers with more than 2 digits before the decimal point and 2 digits after the decimal point. This discrepancy could be causing the upload to hang.

On the other hand, the Float data type in BigQuery is a floating-point number that can represent a wide range of values with varying degrees of precision. It's more flexible than the Numeric data type, but it can also introduce rounding errors. Since your data fits within the range and precision of the Float data type, the upload completes successfully when you use this data type.

To resolve the issue, you could try using the Numeric data type with a higher precision and scale that matches your data. For example, you could use a precision of 10 and a scale of 2 to represent numbers with up to 8 digits before the decimal point and 2 digits after the decimal point. Alternatively, you could continue using the Float data type if the potential for rounding errors is not a concern for your use case.

[ms4446 · Accepted solution]

The issue you're experiencing is likely due to the way BigQuery handles the Numeric data type. The Numeric data type in BigQuery is an exact numeric data type that holds up to 38 digits of precision and 9 digits of scale. This means it can represent very large numbers with a high degree of accuracy, but it also means it requires more computational resources to process.

When you specify a Numeric data type with a precision of 2 and no scale, you're essentially telling BigQuery to expect a number with 2 digits before the decimal point and no digits after the decimal point. However, your data contains numbers with more than 2 digits before the decimal point and 2 digits after the decimal point. This discrepancy could be causing the upload to hang.

On the other hand, the Float data type in BigQuery is a floating-point number that can represent a wide range of values with varying degrees of precision. It's more flexible than the Numeric data type, but it can also introduce rounding errors. Since your data fits within the range and precision of the Float data type, the upload completes successfully when you use this data type.

To resolve the issue, you could try using the Numeric data type with a higher precision and scale that matches your data. For example, you could use a precision of 10 and a scale of 2 to represent numbers with up to 8 digits before the decimal point and 2 digits after the decimal point. Alternatively, you could continue using the Float data type if the potential for rounding errors is not a concern for your use case.

[yanay]

Confirming that above suggestion worked well. Suggesting that the upload exists gracefully with an error message under the condition I described (it does so with other data errors, like text in a numeric field.) What's the recommendation for what's known in SQL circles as "MONEY" field? Thank you

[ms4446 · Accepted solution]

In BQ, the recommended data type for monetary values is NUMERIC or BIGNUMERIC. Both of these data types are exact numeric data types that can represent very large numbers with a high degree of accuracy, making them suitable for financial calculations where precision is important.

The NUMERIC data type in BigQuery can hold up to 38 digits of precision and 9 digits of scale. This means it can represent numbers as large as 99999999999999999999999999999.999999999 and as small as -99999999999999999999999999999.999999999.

The BIGNUMERIC data type is an extension of the NUMERIC type and can hold up to 76 digits of precision and 38 digits of scale. This means it can represent even larger numbers, but it also requires more computational resources to process.

When using these data types to represent monetary values, you would typically set the scale to 2 to represent cents. For example, you could use a NUMERIC data type with a precision of 38 and a scale of 2 to represent monetary values up to 99999999999999999999999999999.99.

It's worth noting that while the NUMERIC and BIGNUMERIC data types provide a high degree of precision, they can also introduce complexity due to the way they handle rounding. If the potential for rounding errors is a concern, you might consider using the FLOAT or DOUBLE data types instead, keeping in mind that these data types are less precise.