3

I have a dataset that contains integer of type “Long”. I need to analyse that dataset by doing some arithmetic operations on it. What are the possible ways to do that?

I have tried casting to FLOAT but then I can't use some operations like bitwise AND etc on that data type.

As an example, here is the value of one field

18446744073709551615

Thanks

john
  • 2,324
  • 3
  • 20
  • 37

2 Answers2

4

You can use numeric if you want to keep a number and all its decimal points:

select cast('18446744073709551615' as numeric)

This is not infinitely flexible. It is actually equivalent to numeric(38, 9) in any other database. BigQuery offers no control over the scale and precision. This is described in the documentation (admittedly, this is a very recent addition to BigQuery).

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Unable to use bitwise operations on it. Error: Bitwise operator & requires two integer/BYTES arguments of the same type, but saw NUMERIC and NUMERIC – john Sep 21 '18 at 01:42
  • @john . . . Ouch. Bitwise operations will require a bit of trickery. But you can always use `* 2` and `/ 2`. – Gordon Linoff Sep 21 '18 at 01:50
  • I had tried to break the logic by breaking big long integer into two small integers and then performing operations on it. Basic operations like +, -, *, / worked fine but things got complicated with bitwise & operation. I would really appreciate if you have any better idea. :) – john Sep 21 '18 at 02:01
  • Can you file a feature request on [the issue tracker](https://issuetracker.google.com/issues?q=componentid:187149%2B)? – Elliott Brossard Sep 21 '18 at 02:31
  • @ElliottBrossard Done :) – john Sep 21 '18 at 02:43
3

The long range for integer is :

The long data type is a 64-bit two's complement integer. The signed long has a minimum value of -2^63 and a maximum value of 2^63-1

In BigQuery normal Integer long is represented by INT64(INTEGER).

Google Big-Query documentation : https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#integer_type.

** Basically, long maps to INTEGER (INT64) which has a larger range in bigquery. **

So, if you keep it as INTEGER type, and the arithmethic operation doesn't result in decimal points to the final value, you should be okay in representing with data type INTEGER in BigQuery.

Nimit Nagpal
  • 31
  • 1