Using InfluxQL(SQL) with InfluxDB 2.x.x

What is InfluxDB?

• It is a time series database thus deals with timestamped data points.
• It handles large amounts of data efficiently because of a faster data ingestion rate.
• Retires Data based on a Life Cycle policy (days, weeks).
• It uses Buckets(combo of database + retention policy) to store measurements(like a table in Relational DBs).

Querying Data

• Previously Influx Query Language was used to query the database, but with recent versions FLUX has become the main scripting source for data retrieval.
• The primary advantage of Flux over InfluxQL is that, it’s simpler to aggregate and analyse over data points.

Migration

With the legacy versions slowly getting obsolete, and the influx cloud’s support for 2.x.x, time will come when you would need to update your database to the latest versions. Now the challenge would be to make accurate updates to your source code from InfluxQL to Flux query. Still there is a way where you could avoid this. And it’s done by creating a DBRP mapping.

What is DBRP mapping?

Database Retention Policy mapping is a procedure to create a mapping of a Bucket to a database and a retention policy. This allows us to use InfluxQL with newer versions of InfluxDB which natively works only with Flux.

How to Create DBRP?

Refer the below API example to create a DBRP mapping.

• Method Type: POST
• URL: https://<Your_InfluxDB_Host>/api/v2/dbrps
  – E.g.: http://localhost:8086/api/v2/dbrps
• Headers:
  – Authorization: Token <YOUR_INFLUX_TOKEN>
  – Content-Type: application/json
• Request Body

 {
    "bucketID": "3d92bdaaee5354e9",
    "database": "sampleBucket001",
    "default": true,
    "org": "myOrganisation",
    "orgID": "620dee4289f75t3g",
    "retention_policy": "sampleBucket001-rp"
}

“bucketID”: The ID of the targeted bucket to whom mapping needs to be done.

“database”: The name of the v1.x.x db that you’ll be using in InfluxQL. Ideally you should give the same name as the bucket name to avoid confusion.

“default”: true if you want to use default retention policy

“org”: The name of the organisation.

“orgID”: The ID of the organisation.

“retention_policy”: Give any name for retention policy you would like to use in combination with the database.

• Response

{
  "id": "0a9575fb8bc3b000",
  "database": "sampleBucket001",
  "retention_policy": "sampleBucket001-rp",
  "default": true,
  "orgID": "620dee4289f75t3g",
  "bucketID": "3d92bdaaee5354e9"
}

How to verify your DBRP was created and is available?

Refer the below API to fetch a DBRP

• Method Type: GET
• URL: https://<host>/api/v2/dbrps/<DBRP_ID>?orgID=<org_id>
  – Eg.: http://localhost:8086/api/v2/dbrps/0a9575fb8bc3b000?orgID=620dee4289f75t3g
• Headers:
  – Authorization: Token <YOUR_INFLUX_TOKEN>
  – Content-Type: application/json
• Response

{
    "content": {
        "id": "0a9575fb8bc3b000",
        "database": "sampledataset",
        "retention_policy": "sampledataset-rp",
        "default": true,
        "orgID": "640bee9277f75f2d",
        "bucketID": "3d92bdaaee5354e9"
    }
}

Now that your DBRP Mapping is ready and valid, you can just continue using your InfluxQL(SQL) queries with the latest InfluxDB version whether it is a standalone server or on the cloud.