BigQuery rows get swapped

Question

I have been trying to create a table in BigQuery from a .csv file stored in my bucket. The table is created and the data is loaded with correct number of rows and columns, however, the rows get swapped in BigQuery for some reason.

I tried to use R connector to push the data from my local machine to BigQuery and the same problem occurs.

So when I do SELECT * FROM , it shows me the complete table inside BigQuery but the rows are swapped (i.e. row 21 becomes row 1, row 4000 becomes row 3 for example).

I will appreciate your response.

Answer 1

As in most SQL-related databases, data stored in BigQuery has no natural order. When you store data in BigQuery it will be automatically sorted in ways that can optimize the execution time of queries.

If you need to preserve the original order, you might need to add an additional column noting the relative order - and then call it out with an ORDER BY on queries.