Using CrateDB with PHP PDO for a Real-Time IoT Project on Alibaba Cloud

By Alex Mungai Muchiri, Alibaba Cloud Tech Share Author. Tech Share is Alibaba Cloud’s incentive program to encourage the sharing of technical knowledge and best practices within the cloud community.

The Internet of Things is on the rise, ushering in a new possibility in device interconnectivity. Prior to the advent of the concept, using data to control vehicles, machines, home appliances and even entire cities seemed like a dream. In the present, however, IoT applications store and analyze data collected by millions of embedded sensors to enable autonomous operations of physical systems. Real-time response in IoT is critical for smooth systems operations and this requires a database to do the following:

  1. Process inputs from millions of data points in real-time
  2. Provide real-time responses from the data
  3. Accommodate varying data structures
  4. Scalable enough to handle complex operations such as time-based, geographical, or machine learning analyses.
  5. Deploy confidently to the cloud

The sheer scale of data volumes and transaction intensity, as well as the complexity of analysis, integration, and machine learning have placed IoT developers up against the wall. Thankfully, CrateDB was designed to handle IoT complexity with ease. In this tutorial, we are going to look at how to deploy CrateDB for your application on an Alibaba Cloud Elastic Compute Service (ECS) server running on Ubuntu 16.04.

Overview of CrateDB

Adaptable Architecture

(Source: crate.io)

Install CrateDB on Linux

sh$ bash -c "$(curl -L install.crate.io)"

The command also takes care of Java 8 installation and other housecleaning tasks.

Import Test Data

We are now going to import some tweets using the Help tab on the admin UI, accessible on your left.

Follow the instructions after selecting Import Tweets for Testing. Try to settle for about 1000 tweets after which you should select the Tables option on the navigation menu. It should lead to the interface below:

The next phase is learning how to query CrateDB using the query console on the admin interface. Access the console interface from the Console tab on the navigation menu

We are going to use this screen to perform query operations of previously imported tweets. Use the Tables tab if you want to see the schema layout. In this example we are going to execute a simple filter query from the tweets collected in our database using the query below:

SELECT *
FROM tweets
WHERE account_user['followers_count'] > 100
LIMIT 100;

The output is tweets from users with more than 100 followers. Once typed into the query console, the operation queries the attributes of the object account_user and selects those whose followers_count is more than 100.

The screenshot below shows the output of the query:

CrateDB supports a wide range of query capabilities such as full-text search, distributed aggregation, as well as more complex scalar functions and data analysis. There’s a whole range of capabilities included in the CrateDB query reference. Below are three other ways to query CrateDB

The CrateDB Shell

sh$ ./bin/crash

Executing queries on CrateDB is as simple as typing the queries and hitting enter as shown in the example below:

SELECT account_user['followers_count']
FROM tweets
ORDER BY account_user['followers_count'] DESC
LIMIT 10;

CrateDB Shell has an auto-completion feature as you would notice when using this feature. The response would be something like this:

+---------------------------------+
| account_user['followers_count'] |
+---------------------------------+
| 1416583 |
| 1076527 |
| 1025614 |
| 886577 |
| 854054 |
| 818439 |
| 795778 |
| 761014 |
| 740071 |
| 673962 |
+---------------------------------+
SELECT 10 rows in set (0.003 sec)

CrateDB HTTP API

Example

SELECT COUNT(*) FROM tweets

We shall use a query to count the number of tweets in our database using HTTPie on localhost:4200. Execute the function below:

sh$ http localhost:4200/_sql stmt="SELECT COUNT(*) FROM tweets"

The JSON response should be as follows:

HTTP/1.1 200 OK
content-length: 71
content-type: application/json; charset=UTF-8
{
"cols": [
"count(*)"
],
"duration": 11.847271,
"rowcount": 1,
"rows": [
[
3879
]
]
}

This response indicates that we have 3879 counts of one row and one column.

Using third-Party Clients

CrateDB PDO driver

Set Up the PDO driver in the Composer

{
"require": {
"crate/crate-pdo":"~1.0.0"
}
}

Install PDO

sh$ composer install

In your PHP application, use the line below to require the autoload.php file in the Composer.

require DIR . '/vendor/autoload.php';

Now we can connect to CrateDB after this task

Connect to CrateDB

crate::

Just remember to replace <HOST_ADDR> with the host address of your Alibaba Cloud ECS where it is installed and <PORT> with a valid HTTP endpoint port number. Below are some sample DSN strings

crate:localhost:4200
crate:crate-1.vm.example.com:4200
crate:198.51.100.1:4200

If you have a cluster of nodes, just remember to use a number to specify the respective node as shown:

crate::,:

There’s no limit to the number of nodes you can have on your cluster, but ensure all nodes are separated using the comma (,) character.

Your client will attempt to connect to each of your cluster’s nodes until one of them connects, with each node being loved to the last position after an unsuccessful connection attempt. CrateDB is able to achieve some sort of round-robin load balancing using this behavior.

Now, if you want to connect to a specific schema, specify it as below:

crate::/

The <SCHEMA> should be the name of a schema in your database, if you don't, the default 'doc' will be used. Based on this requirement, we can have the following DNS strings

crate:localhost:4200/iot_schema
crate:crate-1.vm.url.com:4200,crate-2.vm.url.com:4200
crate:198.161.100.1:4200,198.161.100.2:4200/my_schema

Get a Connection

use Crate\PDO\PDO as PDO;$dsn = '<DATA_SOURCE_NAME>';
$user = 'crate';
$password = null;
$options = null;
$connection = new PDO($dsn, $user, $password, $options);

CrateDB 2.1.x supports authentication, but earlier models do not require that you supply a username and password. Furthermore, CrateDB superuser does not require a password, which means that you don’t need the argument above. However, that should only apply if you have not configured a custom database user.

Advanced Settings

$connection->setAttribute(, );

The <ATTRIBUTE> section requires a PDO attribute class constant, as indicated in this setAttribute guide.

The <VALUE> is a value for the corresponding class constant, something like this:

PDO::ATTR_TIMEOUT

The attribute above represents timeout in seconds, whereby the HTTP connection drops after that time elapses. The default is 0.

Driver Constants

PDO::CRATE_ATTR_DEFAULT_SCHEMA (string)

It is the default PDO connection schema. Nonetheless, it is possible to query any schema in the database.

Under the SSL mode, you can specify the following:

PDO::CRATE_ATTR_SSL_MODE (int) (named attribute)

Disabling the SSL mode:

CRATE_ATTR_SSL_MODE_DISABLED (default)

Disable requirement for verification in SSL mode:

CRATE_ATTR_SSL_MODE_ENABLED_BUT_WITHOUT_HOST_VERIFICATION

Perform verification mode in SSL mode:

CRATE_ATTR_SSL_MODE_REQUIRED

Link up to client key file

PDO::CRATE_ATTR_SSL_KEY_PATH (string)

SSL client password

PDO::CRATE_ATTR_SSL_KEY_PASSWORD (string)

SSL certificate path

PDO::CRATE_ATTR_SSL_CERT_PATH (string)

File with SSL certificate password

PDO::CRATE_ATTR_SSL_CERT_PASSWORD (string)

SSL Certificate Authority (CA) file

PDO::CRATE_ATTR_SSL_CA_PATH (string)

There is a whole range of operations to use for setting up SSL certificates in this reference.

PDO Fetch Modes

And that’s how you set up CrateDB using PHP PDO on Alibaba cloud running on Ubuntu or any Linux variant. You can find further instructions here in case you are stuck (http://www.php.net/manual/en/intro.pdo.php).

Why Use CrateDB

CrateDB is based on SQL

CrateDB Uses NoSQL-style Storage and Indexing

  1. Lucene: stores and indexes data
  2. Elasticsearch: logging transaction and masterless clustering
  3. Netty: networking between nodes asynchronously

Dynamic Schemas

Quick INSERTs

Real-time querying

Portable

Conclusion

Do you have an Alibaba Cloud account yet? Sign up for an account and try over 40 products for free worth up to $1200. Get Started with Alibaba Cloud to learn more.

Reference:

https://www.alibabacloud.com/blog/using-cratedb-with-php-pdo-for-a-real-time-iot-project-on-alibaba-cloud_593855?spm=a2c41.11828478.0.0

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.