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

  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

Overview of CrateDB

Adaptable Architecture

Install CrateDB on Linux

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

Import Test Data

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

The CrateDB Shell

sh$ ./bin/crash
SELECT account_user['followers_count']
FROM tweets
ORDER BY account_user['followers_count'] DESC
LIMIT 10;
+---------------------------------+
| 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

SELECT COUNT(*) FROM tweets
sh$ http localhost:4200/_sql stmt="SELECT COUNT(*) FROM tweets"
HTTP/1.1 200 OK
content-length: 71
content-type: application/json; charset=UTF-8
{
"cols": [
"count(*)"
],
"duration": 11.847271,
"rowcount": 1,
"rows": [
[
3879
]
]
}

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
require DIR . '/vendor/autoload.php';

Connect to CrateDB

crate::
crate:localhost:4200
crate:crate-1.vm.example.com:4200
crate:198.51.100.1:4200
crate::,:
crate::/
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);

Advanced Settings

$connection->setAttribute(, );
PDO::ATTR_TIMEOUT

Driver Constants

PDO::CRATE_ATTR_DEFAULT_SCHEMA (string)
PDO::CRATE_ATTR_SSL_MODE (int) (named attribute)
CRATE_ATTR_SSL_MODE_DISABLED (default)
CRATE_ATTR_SSL_MODE_ENABLED_BUT_WITHOUT_HOST_VERIFICATION
CRATE_ATTR_SSL_MODE_REQUIRED
PDO::CRATE_ATTR_SSL_KEY_PATH (string)
PDO::CRATE_ATTR_SSL_KEY_PASSWORD (string)
PDO::CRATE_ATTR_SSL_CERT_PATH (string)
PDO::CRATE_ATTR_SSL_CERT_PASSWORD (string)
PDO::CRATE_ATTR_SSL_CA_PATH (string)

PDO Fetch Modes

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

--

--

--

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Version Controlling

Ag·ile: A Software Development Methodology

Has Software Engineering Become Boring?

Practical bit manipulation in JavaScript

Learning To Learn

uPort is fully open-source now!

What is Apache Kafka

Getting Started with Python Part-2

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Alibaba Cloud

Alibaba Cloud

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com

More from Medium

Securely Connect to Redis and Utilize Benchmark Tools

Deploy Standalone (Single Node) MinIO server using Docker Compose on Linux

Setting up GCP Compute Engine for Grafana: Grafana Series -Part 1.

Composite GitHub Actions for Deploying GCP resources to multiple projects