0

We are using Legacy SQL on a specific request. We can't use standard SQL for some internal reasons.

We would like to paginate our results, because we have a lots of rows. Like that :

SELECT ... FROM ... LIMIT 10000 30000 // In standard SQL

But in Legacy SQL Offset don't exists. So how to do the same job ?

Edit :

I don't want to order. I want to paginate. For example get 1000 rows after skipping 2000 rows. A simple LIMIT clause with an offset, like in traditional SQL Database or like in BigQuery Standard SQL. To do this, I want to use Big Query Legacy SQL.

jeremieca
  • 1,156
  • 2
  • 13
  • 38
  • Please clarify--are you trying to get sorted results? Or just to list (generally unsorted) results of a query? Which client API are you using? – Elliott Brossard Mar 20 '18 at 11:47
  • Done in edit. :) – jeremieca Mar 20 '18 at 12:29
  • The reason I ask about the API that you are using (it doesn't look like you've addressed this question) is that there is explicit [API/library support for pagination](https://cloud.google.com/bigquery/docs/paging-results). Or are you just using the UI? It's not clear from your question. – Elliott Brossard Mar 20 '18 at 12:34
  • I would understand that for first page you would not care about order - but as soon as you will ask for second page - you implicitly introducing order. One of option I see - you might need just first page - so it might be just about random 20000 rows? what other options? So, what is your real use case? why you think you need paginating while no order? – Mikhail Berlyant Mar 20 '18 at 12:43
  • Ok. I would like to read the contact of table part by part. This table has no insert, no update and no delete on it. So from my point of view, order don't change between request, isn't it ? For now, we are just using the UI, but at the end, we use the Node.js driver. But I don't really understand how the paging works in Node by reading the doc. :s Thanks for your help. – jeremieca Mar 20 '18 at 12:51

1 Answers1

3

The pagination you talking about is done via tabledata.list API

Based on your question and follow-up comments - It might be the way for you to go. Even though it does not involve querying. Just API or related method in client of your choice.
pageToken parameter allows you to page result
Btw, another benefit of this approach - it is free of charge

If you still need to do pagination via query - you option is using ROW_NUMBER() In this case - you can prepare your data in temp table with below query 

SELECT <needed fields>, ROW_NUMBER() OVER() num
FROM `project.dataset.table`

Then, you can page it using num 

SELECT <needed fields>
FROM `project.dataset.temp`
WHERE num BETWEEN 10000 AND 30000
Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230