Big Data Served in Bite-Sized Pieces – Pagination with the Fleetsu API

Today we continue our series on the Fleetsu REST API with a tutorial on how to make larger volumes of data easier to access through gaining mastery of pagination.

What is Pagination?

Have you ever accessed or downloaded a large amount of data only to notice that the total number of records just happened to be a suspiciously round number? How likely is it that the number of ‘ignition on’ events in NSW for the last four months is exactly 1000?

In all likelihood you have brushed up against an internal barrier that was created to prevent excessive strain on database resources. These barriers are called pages, just like a book, and to successfully retrieve all the data in your query you’ll need to learn how to turn the pages.

A Brief Tutorial

Let’s start out with a straight call to the assets API.  https://app.fleetsu.com/v1/assets

At the top of the response you will see a lovely little header that shows the number of results we are returning. 

This is the default limit for this record type – 1000 records. You can think of this like the maximum number of words that can fit on a single page. In order to ‘turn the page’ we have to adjust the API call using two parameters “_start” & “_limit”.

“_start” specifies which record number we want the API to start the return on.

“_limit”  indicates how many records we want to return on the dataset (words per page). 

Therefore if we want to step through 1200 Records, 200 records at a time we would use the following endpoints. 
 

https://app.fleetsu.com/v1/assets?_start=1&_limit=200
https://app.fleetsu.com/v1/assets?_start=201&_limit=200
https://app.fleetsu.com/v1/assets?_start=401&_limit=200
https://app.fleetsu.com/v1/assets?_start=601&_limit=200
https://app.fleetsu.com/v1/assets?_start=801&_limit=200

The next one we do will exceed our original page limitation.

https://app.fleetsu.com/v1/assets?_start=1001&_limit=200

With the Fleetsu API, you can reference a number higher than actually exists. In this case the API call will simply respond that there weren’t any records found in the specified range.

Knowing this is all well and good. However, implementing it in something like PowerQuery can be very daunting. Let’s step through that now.

If you don’t have a basis in Power Query, I recommend opening a new tab and having a quick read of our Power Query primer so that you can follow along. 

Firstly, make a straight call to assets within the Fleetsu API

Next, in the grey sidebar on the left-hand side, right click on your query (in this case “assets”) then select “Advanced Editor”

The Advanced Editor should look something like this:

For us to move through pages automatically as we hit limits we’ll need this to change rather drastically. Here’s the commented code to paste into that section. For this example we will be moving through assets 500 at a time:

let
    // This is the URL I am querying
    baseUrl = "https://app.fleetsu.com/v1/assets",
    // This is a little function to get a URL with any parameters passed
    GetJson = (Url) =>
        let 
            RawData = Web.Contents(Url),
            Json    = Json.Document(RawData)
        in  Json,
    // This allows me to pass in the Start Position with the baseURL
    GetData = (start) =>
        let startPosition  = "?_start=" & Number.ToText(start),  
            Url   = baseUrl & startPosition,
            Json  = GetJson(Url)
        in  Json,
  results = List.Generate(
        ()=>[ start = 1, items = GetData(start)],
        each not List.IsEmpty([items][results]),
        each[start = [start] + 500,items = GetData(start)]
    ),
    #"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

Here’s what it looks like when pasted into Power Query. We’ll explain how it works in a second, but first let’s see what it can do!

From there you will see the following record list. In this case, rather than representing individual assets as it did in our previous tutorial, each row represents an entire page of 500 assets, but we still need to expand all of them in order to get at the juicy data inside.  

From here we will be pressing what looks like the same button multiple times and getting different results with each click.

For our first click of the expander button (in red below) we want to select items from the popup. As always, untick “Use original name as prefix”.

On our next click we want to only select results.

Our last click will let us select all of the fields that we want to display against each asset – pick as many as you like. When you click OK you will see all of the data unpackaged with all of its fields. From here it’s Close and Load and the rest occurs in Excel proper.

The code

Let’s delve into the code a little bit more, so that you know which bits to tweak, as you will inevitably have to. 

Please keep in mind that this is not a complete breakdown of the Power Query M Language, it’s just enough to give you an overview.

GetJson – This is the function that grabs the data from a URL and returns it as JSON

 GetJson = (Url) =>
        let 
            RawData = Web.Contents(Url),
            Json    = Json.Document(RawData)
        in  Json,

GetData – This calls the GetJson function but adds the correct “_start” parameter.  Note: If you have multiple filtering parameters you will need to change the “?_start” to “&_start” as this function assumes the “_start” is the first parameter

    GetData = (start) =>
        let startPosition  = "?_start=" & Number.ToText(start),  
            Url   = baseUrl & startPosition,
            Json  = GetJson(Url)
        in  Json,

List.Generate and List.IsEmpty – This iterates through each of the GetData calls incrementing the parameter by 500 records each time until there are no results returned (List.IsEmpty). To make a smaller page simply change 500 to whatever number you like. If you start going above 1,000 you might hit a hard limit for what the API will return in a single page, depending on the dataset you are querying.

  results = List.Generate(
        ()=>[ start = 1, items = GetData(start)],
        each not List.IsEmpty([items][results]),
        each[start = [start] + 500,items = GetData(start)]
    ),

In short, the code above simply keeps interrogating the API for more pages full of information, increasing the _start position until no additional results are returned. In this way you can overcome the default limitations against a single API call and start to drink deeply from your automotive data.

Bonus Prize!

For an added bonus here is a FREE code snippet that runs through Fleetsu’s pagination in Python.

import requests
import json
#Define User Name and password
user = 'myuseraccount@fleetsu.com'
password = 'setecastronomy'
#Setup a session to connect to FLeetsu
session = requests.Session()
session.auth = (user, password)
#Define Initial URL and Start Parameter
start = 1
noMoreResults = False
url = 'https://app.fleetsu.com/v1/assets'
start = 1
params = {'_start' : start, '_limit' : 500} 
#Define and object to store results set
allEntries = []
noResults = False
# Loop through and populate results until no results are found
while not noResults:
    results = session.get(url=url, params=params).json()
    allEntries.extend(results['results'])
    start += 500
    params = {'_start' : start, '_limit' : 500}
    if not results['results']:
        noResults = True

Happy Paging!