Monday, June 19, 2017

Update on MySQL Document Store and Node.JS

Opps!

I admit my JavaScript skills are rusty and dusty. Plus I am new to Node.JS. So yesterdays blog drew some very helpful comments from two of MySQL's best. Johannes Schl├╝ter and Rui Quelhas let me know there was a better way to code the example. Much thanks to them.

Better Example

// Simple example to grap one record and print it
const mysqlx = require('@mysql/xdevapi');
const options = {
  host: 'localhost',
  port: 33060, // should be a number
  dbUser: 'root',
  dbPassword: 'Hell0Dave!'
};

mysqlx
  .getSession(options)
  .then (session => {
  var schema = session.getSchema('world_x');

//equivilent of SELECT doc FROM countryinfo where _id = 'USA'
  var coll = schema.getCollection('countryinfo');
  var query = "$._id == 'USA'";

      // Print doc 
     return Promise.all([
       coll.find(query).execute(function (doc) {
       console.log(doc);
      }),
      session.close()
     ]);
    })
    .catch(err => {
        console.log(err.message);
        console.log(err.stack);
    });

Modern Code

This cleans up the error catching and the Promise.all helps put all the heavy lifting together.

And the PHP XDevAPI is in PECL

And you can access the XDevAPI from PHP using the PECL Extension!

Sunday, June 18, 2017

MySQL Document Store and Node.JS

JavaScript and MySQL

Last time I looked at using the new MySQL Shell in JavaScript mode to access documents. I promised that I would look at indexes this time but a few folks wanted to see how to use the MySQL Document Store with Javascript -- the language not the MySQL Shell mode-- specifically with the Node.JS connector. And they ask for a very simple example. So Indexes in the future, Node.JS now.

So this is a simple on using the MySQL Document Store from Node.JS.

Install

Download the Node.JS connector and then follow the very simple installation instructions. And use your OS's instictions for installing Node.JS.

The documentation on using the Node.JS Connector with the XDevAPI is not written for folks just starting with either the API or Node.JS.

Example


const mysqlx = require('@mysql/xdevapi');

mysqlx.getSession({             // Create session
  host: 'localhost',
  port: '33060',
  dbUser: 'root',
  dbPassword: 'Hell0Dave!'
}).then(function (session) {    // USE world_x
  var schema = session.getSchema('world_x');

//equivilent of SELECT doc FROM countryinfo where _id = 'USA'
  var coll = schema.getCollection('countryinfo');
  var query = "$._id == 'USA'";

      // Print doc 
  coll.find(query).execute(function (doc) {
    console.log(doc);
  })
  .catch(function (err) {
    console.log(err.message);
    console.log(err.stack);
  });
  session.close();
}).catch(function (err) {
  console.log(err.message);
  console.log(err.stack);
});

Output


node countryinfo.js
{ GNP: 8510700,
  _id: 'USA',
  Name: 'United States',
  IndepYear: 1776,
  geography: 
   { Region: 'North America',
     Continent: 'North America',
     SurfaceArea: 9363520 },
  government: 
   { HeadOfState: 'George W. Bush',
     GovernmentForm: 'Federal Republic' },
  demographics: { Population: 278357000, LifeExpectancy: 77.0999984741211 } }

Wrap-up

So that is a simple of an example that I could think of. I plan to explore more of using the XDevAPI (and fingers crossed the PHP version appears soon) and let me know if there are anything you want to see. And I will get to indexes while I am in Glasgow next week.

Wednesday, June 14, 2017

MySQL Document Store: Getting Started

MySQL's new NoSQL database is quite a big change for all used to using a SQL speaking relational database. The Document Store makes use of the new MySQL Shell, the new X Protocol, and the new X Devapi to provide a NoSQL document storage service. This provides a way for developers to have a schema-less, NoSQL data store to build services and applications is a quick fashion without having to design a schema, normalize data, develop queries in SQL, or any of the rest of the overhead needed to create a relational databases. Behind the scenes and invisible to the developer is that the schema-less data is stores in a JSON type column in a MySQL instance.

Please note that I am using JavaScript Mode (instead of SQL or Python) for these examples. No special reason.

First Step

The new MySQL Shell is a separate install from the server software. Once installed it will provide the ability to use MySQL as a document store.

The db object is how we can communicate to the server and the world_x data. Now we can create a simple collection.

Whoa! Something is wrong, isn't it?

The Document Store adds an _id column automatically. You can specify your own _id but be warned it is defined by default as an UNIQUE index, so no duplicates. The InnoDB storage engine desires a index and will pick a column to index if you do not specify one (usually not a great choice in a column BTW). But JSON columns can not be directly indexed. But do not worry about the details but do note that the _id column is there.

Also notice the order of the data returned. The MySQL JSON data type organizes the data in a special binary format for faster look ups and the side effect is the alphabetizing of the keys in the key/value pairs.

Filters

Filters can be added to find specific records and to return just the desired information (default is like a SELECT *).

You can bin values to variables to separate the data from the condition.

Modify Data

If you are used to UPDATE set x=y WHERE z=n then the next part may look a little odd.
You can add more than simple key/value pairs. Here is an example of adding an array.
And of course you need the compliment to set, unset.

We can also add to or remove items for arrays within the data. The $ is short hand for the 'current document'.

And of course db.foobar.remove() would delete all the records in the collection.

Next Time: Indexes and Documents

This time we skimmed the surface of the MySQL Document Store but next time we will look at getting some extra performance by using indexes.

Wednesday, May 24, 2017

Import a JSON Data Set into MySQL

JSON Data Into MySQL

JSON is now a very hot format for sharing data and MySQL's 5.7 Data Set is now a very hot way of storing that data. But I have had some requests on getting a raw JSON data set into MySQL. So lets start with Global Airfields data from the Awesome JSON Datasets collection.

airfields.json

The data is in a file named airfields.json and comes as one very long line. MySQl has now way of taking out the individual JSON documents from that string and putting them in a row. The jq tool will let use see the data broken down into objects. Try jq -C '.[]' airfields.json and you will see individual documents colorized.

{
  "ID": "LFOI",
  "ShortName": "ABBEV",
  "Name": "ABBEVILLE",
  "Region": "FR",
  "ICAO": "LFOI",
  "Flags": 72,
  "Catalog": 0,
  "Length": 1260,
  "Elevation": 67,
  "Runway": "0213",
  "Frequency": 0,
  "Latitude": "N500835",
  "Longitude": "E0014954"
}
{
  "ID": "LFBA",
  "ShortName": "AGENL",
  ....
  "Latitude": "N492658",
  "Longitude": "E0060730"
}

So now we can see the key/value pairs in each objects. So lets save that information into a text file but use the -M not -C option by typing jq -M '.[]' airfields.json > af.

The airport Table

We will need a simple table as in CREATE TABLE airport (doc JSON); to hold the data. Now the data set is fairly small and an text editor like vi can be used to turn the af into a SQL statement. The first line should read INSERT INTO airport (doc) VALUES ('{. The very last line needs to be }');. The second through last {s need to changed to ('{ and the }s (save the last one) need to be come }').

Login to MySQL and type SOURCE af to load the data. I used the s or search operator in vi but I could have used another editor like sed. Then it is time to feed the data into new table.

mysql> source afm
Query OK, 1218 rows affected (0.16 sec)
Records: 1218  Duplicates: 0  Warnings: 0

'
mysql> SELECT doc FROM airport LIMIT 1;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| doc                                                                                                                                                                                                                                     |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"ID": "LFOI", "ICAO": "LFOI", "Name": "ABBEVILLE", "Flags": 72, "Length": 1260, "Region": "FR", "Runway": "0213", "Catalog": 0, "Latitude": "N500835", "Elevation": 67, "Frequency": 0, "Longitude": "E0014954", "ShortName": "ABBEV"} |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Indexes?

We can easily build indexes on columns built on data in the documents. But what keys exist?

mysql> SELECT JSON_KEYS(doc) FROM airport LIMIT 1;
+------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_KEYS(doc)                                                                                                                           |
+------------------------------------------------------------------------------------------------------------------------------------------+
| ["ID", "ICAO", "Name", "Flags", "Length", "Region", "Runway", "Catalog", "Latitude", "Elevation", "Frequency", "Longitude", "ShortName"] |
+------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

We could index all or a combination of any of these keys. Please note that all the records had the same format which can be rare for schemaless data. Let's take Region for an example. Region sounds important enough to need indexing. But is it a good candidate for an index? Here we have to peer at the data and unfortunately every record has 'FR' for a value. Indexing Region does not help pare down the search for specific record or records.

What about Name? Looking at the data shows that there are no duplicates in that field. As would Latitude and Longitude. Heck, longitude and latitude are often used together so we could put both into one index.

mysql> ALTER TABLE airport add column Name CHAR(30) GENERATED ALWAYS AS (doc->"$.Name");
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX nameidx ON  airport (Name);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

And we can test the index easily.

mysql> explain select Name from airport where Name="ABBEVILLE";
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | airport | NULL       | ref  | nameidx       | nameidx | 31      | const |    1 |   100.00 | Using index |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


And then the column for Longitude and Latitude.

mysql> ALTER TABLE airport add column longlat CHAR(30) GENERATED always  AS (concat(json_unquote(doc->"$.Longitude"),"X",json_unquote(doc->"$.Latitude"))) stored;
Query OK, 1218 rows affected (0.39 sec)
Records: 1218  Duplicates: 0  Warnings: 0

The index for this new column's index is left up to you for an exercise.

But now we have a table built from raw JSON formatted data with indexes we can use for quick searches.

Tuesday, April 18, 2017

New MySQL JSON Functions (more)

MySQL 8 is going to have new batch of JSON functions and last time JSON_PRETTY() was covered in some details. The recent release of 8.0.1 provides an opportunity to try these new functions and a few that might have been missed with 8.0.0.

Unquoting

The -> shortcut for JSON_EXTRACT() was introduced with MySQL 5.7. And now there is the unquoting extraction operator or ->> to simplify things again! Think of it as JSON_UNQUOTE wrapped around JSON EXTRACT. The following there queries produce the same output.

Aggregation

The new JSON_ARRAYAGG() and JSON_OBJECTAGG() takes a column or column argument and crates an array or object.

Clear as mud?

Well, examine this example:

The two rows from table foo are combined to make a two element array.

The JSON_OBJECT() function takes pairs of columns, assumes they are a key/value pair, and combines them. Note that non-JSON columns and data from JSON columns can be combined, as well as literal strings.

Monday, April 17, 2017

Pretty JSON with JSON_PRETTY()

JSON is a great way to share data and is highly praised in being human readable. Well, compared to XML and some other standards it is easier to read. But sometimes the data looks mushed or wilted. And 'easy' is relative, right? Well, what if you had a way to make it easier to read, er, pretty?

MySQL 8.0.1 has a few new JSON functions but one of them is designed to help make your JSON data look pretty. Place JSON_PRETTY around the column desired and out comes better looking JSON data.

Wow! That is an improvement!

But wait! There's more

JSON_PRETTY can also help make non-JSON data pretty JSON. Using JSON_ARRAY() or JSON_OBJECT to create JSON-ized versions of data and then passing those to JSON_PRETTY produces the desired effect.

Cool!

Being a long time programming with languages that used a lot of braces and brackets, I have found it necessary when not using a help IDA that counted matches for brackets/braces for me to hunt for that missing or extra bracket/braces. Looking at JSON data from a simple select can quickly tire you eyes trying to guess how things are grouped in objects. But it is evident between the first and second examples how nicely JSON_PRETTY presents the data.

Wednesday, February 8, 2017

MySQL 8 Invisible Indexes

MySQL 8 features invisible indexes. But what good is an index if you can see it?

Is It Really Invisible

You probably know by now that indexes can really speed up data searches. And many of you have mastered the use of EXPLAIN to see if the optimizer is using an index for your query. Which means you should know that sometimes for some queries an index does not help.

mysql> CREATE TABLE t2 (
    ->   i INT NOT NULL,
    ->   j INT NOT NULL,
    ->   UNIQUE j_idx (j)
    -> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t2 values (1,2),(3,4),(5,6),(7,8);
Query OK, 4 rows affected (0.00 sec)

mysql> explain select j from t2 where j>2;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t2    | NULL       | range | j_idx         | j_idx | 4       | NULL |    3 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> 

So a simple select of j with values over 2 uses j_idx.

Hocus pocus - Your Index is now Invisible

But what if we are not sure if that index is really helping? Deleting an index for testing (and then rebuilding) can be a time consuming task. With MySQL 8 you simple make the index invisible.

mysql> alter table t2 alter index j_idx invisible;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select j from t2 where j>2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> 

In this overly simple example it is easy to see that the query is not using a key, does not know of any possible keys, and had to read more rows to answer the query. So the j_idx query was helping for this case. You can also know if this is true by looking for errors occuring for queries that include index hints that refer to the invisible index, Performance Schema data shows an increase in workload for affected queries, or suddenly the query starts appearing in the slow query log.

Not for Primary keys

Any key can be made invisible except implicit or explicit primary keys. And it is storage engine neutral from MySQL 8.0.1 onward but 8.0.0 can only works with InnoDB. Please read the manual for more detail.

Presto-Change-o

But how do you reverse the invisibility?


mysql> alter table t2 alter index j_idx visible;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0