A Simple Python Example Program for the MySQL Document Store

Last time we looked at a simple example program using the X Devapi and Node.JS. This time lets look at the Python version. Well, not actually the same. This time instead of looking for the Canadian record, the program limits the query to the first two records found.

Besides Python 2.7, you will need to install the Google Protobuf code plus the development release of the Python Connector and a recent version of MySQL 5.7.

The Code

import mysqlx

session = mysqlx.get_session({          # Authenticate to server
  'host':       'localhost',
  'port':       33060,
  'user':       'dstokes',
  'password':   'S3cR3t%'})

# Connect to Schema 'world_x'
schema = session.get_schema('world_x');

# Set collection to 'countryinfo'
collection = schema.get_collection('countryinfo')

# Ask for two records
result = collection.find().limit(2).execute()

docs = result.fetch_all()

# Print requested records
for i, data in enumerate(docs):
   print "{iteration}: {data}".format(iteration = i, data=data)

# Clean up
session.close()

A Note About the 'Collection' versus 'Table'

Take a quick peek at the table used for the example from the world_x database (see last entry for location and details for installation). There are actually two '_id's in the data. There is one that is the generated column you see below and the other is in the JSON column named doc. The collection itself is the JSON data. How to prove? Change the line starting with result in the above example and replace it with result = collection.find('GNP > 100000').limit(2).execute() or something similar with a key from the data.

mysql> desc countryinfo;
+-------+-------------+------+-----+---------+------------------+
| Field | Type        | Null | Key | Default | Extra            |
+-------+-------------+------+-----+---------+------------------+
| doc   | json        | YES  |     | NULL    |                  |
| _id   | varchar(32) | NO   | PRI | NULL    | STORED GENERATED |
+-------+-------------+------+-----+---------+------------------+
2 rows in set (0.00 sec)

Results

$python test.py
0: {"GNP": 828, "Name": "Aruba", "government": {"GovernmentForm": "Nonmetropolitan Territory of The Netherlands", "HeadOfState": "Beatrix"}, "demographics": {"LifeExpectancy": 78.4000015258789, "Population": 103000}, "_id": "ABW", "IndepYear": null, "geography": {"SurfaceArea": 193, "Region": "Caribbean", "Continent": "North America"}}
1: {"GNP": 5976, "Name": "Afghanistan", "government": {"GovernmentForm": "Islamic Emirate", "HeadOfState": "Mohammad Omar"}, "demographics": {"LifeExpectancy": 45.900001525878906, "Population": 22720000}, "_id": "AFG", "IndepYear": 1919, "geography": {"SurfaceArea": 652090, "Region": "Southern and Central Asia", "Continent": "Asia"}}

Next Time

Next time we will build on these two simple example programs.

A Simple Node.JS Example Program for the MySQL Document Store

I have chatting with others who want to use the new X Devapi Document Store features but have not been able to find simple example programs. Finding the bridge between 'Hello World' and something useful can often be discouraging.

Example Code

Here is a very short Node.JS Docstore example program that accesses the 'countryinfo' collection. More on why Node.JS and what software you need below. But for now regard this code to get one specific record from the database.

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

mysqlx.getSession({             //Auth to server
        host: 'localhost',
        port: '33060',
        dbUser: 'root',
        dbPassword: 'password'
}).then(function (session) {    // use world_x.country.info
     var schema = session.getSchema('world_x');
     var collection = schema.getCollection('countryinfo');

collection                      // Get row for 'CAN'
  .find("$._id == 'CAN'")
  .limit(1)
  .execute(doc => console.log(doc))
  .then(() => console.log("\n\nAll done"));

  session.close();
})

Code Explained

On the first line the X Devapi library is loaded. Line three is authentication to the server followed by getting to the world_x/countryinfo schema. The we find the record for Canada. Is this simpler than "SELECT * FROM countryinfo WHERE _ID = 'CAN'"? Maybe, but I also see where this approach has some facets that need exploiting, er, exploring.

Results

So what does the code do?

:~/xdevn$ node demo.js
{ GNP: 598862,
  _id: 'CAN',
  Name: 'Canada',
  IndepYear: 1867,
  geography: 
   { Region: 'North America',
     Continent: 'North America',
     SurfaceArea: 9970610 },
  government: 
   { HeadOfState: 'Elisabeth II',
     GovernmentForm: 'Constitutional Monarchy, Federation' },
  demographics: { Population: 31147000, LifeExpectancy: 79.4000015258789 } }


All done

Why Node??

I like to try to expand my programming skills every year. MySQL recently released a new driver for the X Devapi Document Store for those who program in Node.JS. JavaScript has become the 'and' in the way many developers describe their jobs. They program in their favorite language 'and' in JavaScript to get their work done. PHP, Python, Rails and the like all have their coders also writing JavaScript. I have not done anything series with JavaScript since they first started trying to call it ECMAscript. At the recent ConFoo I started asking about learning modern JavaScript (and variants) and was pointed at Node.JS. Those of you looking to add Node.JS to your skill set can benefit from the books from Leanpub.com and their Node books (Inexpensive, easy to read, and wonderfully detailed).

The new Connector/Node.JS

First download the Node.JS connector for the MySQL Document Store. Also grab a copy of the world_x database, an update of the World database MySQL has been using for a long time in examples. Follow the directions for installing both of these packages. You will also need to load Google's Protobuffers.

The tutorial is very good but I needed a small stepping stone.Hopefully I can build on this and add the analogs in Python too.

2017 Database Administrator Holiday Gift Guide

TO DBAs:Database Administrators and other computer type folks are very hard to buy presents for at this time of year. Please help your family and friends out out by printing this off and notating which of the following items your would find acceptable.

TO The Friends and Family of a DBA: Yes, they are a pain in the pa-toot to buy a holiday gift for but hopefully this lest will make things a little easier. There are a lot of t-shirt on this list and this is a change to get that one threadbare relic from a long ago computer conference sent to the Valhalla for mangy cotton blend fabrics. Good luck and hopefully you will see something below that will keep you from just handing over a gift card. o

Amazon

Need to work on your laptop in bed or a variety of angles? Then this Furinno X7-BK Ergonomics Aluminum Adjustable Cooling Fan Laptop Tray Table is what you want, available in many colors.

Zazzle

Zazzle.com has a nice collection of DBA centric gifts.
And in female styles also.

Think Geek

Infectious Disease Ball

Cafe Press

Cafe Press has a line of t-shirts, also in m/f sizes and styles
Mugs too!

TeeSpring

Teespring has some interesting shirts

Bonanza

Bonanza.com also has shirts

Uncommongood

Yes, we al have too many phone chargers. Here is uncommongoods.com way to help identify them uniquely.

Other suggestions: Passes for movie theaters (new Star Wars movie on the way), Amazon gift cards, or Bacon of the Month Club membership.

Testing MySQL 8

MySQL 8

MySQL 8 is now available for testing. The list of features is impressive and I am sure many PHP developers will be interested in having a true data dictionary, invisible indexes, and more. Right now it is in a milestone release. I am still doing a lot of work with 5.7 and found a great way to have 8 around when I needed by using Docker. Follow the Docker install guide for your platform of choice and then run

docker run --name mysql8 -e MYSQL_ROOT_PASSWORD=hidave -d mysql:8.0

Checking to See if it Really Works

 docker ps -a
CONTAINER ID        IMAGE               COMMAND                  CREATED              STATUS              PORTS               NAMES
ae1824fc01b5        mysql:8.0           "docker-entrypoint.sh"   About a minute ago   Up About a minute   3306/tcp            mysql8

Note that our instance of MySQL 8 has the container id of ae1824fc01b5. And odds are your container id will be anything but ae1824fc01b5.

Connect to the Container

 docker exec -it ae1824fc01b5 bash

Start up the MySQL command line interface.

# mysql -u root -p       
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 8.0.0-dmr MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

MySQL 8 is running. There is no data loaded. To be able to do that from the host box, we will need to talk to the MySQL Container. And to do that we need the IP Address.

ip a show eth0                                                                                                       
6: eth0@if7:  mtu 1500 qdisc noqueue state UP group default 
    link/ether 02:42:ac:11:00:02 brd ff:ff:ff:ff:ff:ff
    inet 172.17.0.2/16 scope global eth0
       valid_lft forever preferred_lft forever
    inet6 fe80::42:acff:fe11:2/64 scope link 
       valid_lft forever preferred_lft forever

Exit off the container. Now you can load your data and I used the world_x test database mysql -u root -p -h 172.17.0.2 < world_x.sql

Test!

#!/usr/bin/php
connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

$res = $mysqli->query("SELECT Name FROM city ORDER BY Name limit 7");

echo "City names...\n";
for ($row_no = $res->num_rows - 1; $row_no >= 0; $row_no--) {
    $res->data_seek($row_no);
    $row = $res->fetch_assoc();
    echo " Name = " . $row['Name'] . "\n";
}

Now for the first data out of MySQL 8 in the container

shell>php test.php
City names...
 Name = Abakan
 Name = Abaetetuba
 Name = Abadan
 Name = Aba
 Name = Aalborg
 Name = Aachen
 Name = A Coru�a (La Coru�a)

So the test environment is set up. Onto the tests of MySQL 8.

NomadPHP and MySQL JSON Lightening Talk

I will be presenting a short lightening talk on MySQL's JSON Data Type for NomandPHP on Thursday the 13th of October

. Chuck Reeves will be doing the main presentation on How the 3rd Normal Form Destroyed a Family. Get your tickets here.

Moving data from MongoDB to MySQL's JSON Document Store

I had an interesting phone call from someone wanting to move from MongoDB to MySQL's new JSON Document Store. The big question was 'How do I export my Mongo data into something I can read into MySQL?"

The good news is that the folks at Mongo have given us the tools for the hard part of a simple process. For this example I am going to use some test data create previously in a database creatively named dave. The numbers are made up and I am using my name and the names of the canines residing in my home. So a quick peek at the data:

$ mongo
MongoDB shell version: 3.2.8
connecting to: test
> use dave
switched to db dave
> db.dave.find()
{ "_id" : 123, "name" : "Dave" }
{ "_id" : 456, "name" : "Jack" }
{ "_id" : 789, "name" : "Dexter" }
{ "_id" : 787, "name" : "Boo" }
> 
bye

Dumping That Data

First use mongodump -d dave to write out the data much as you would with mysqldump. Under you current working directory of your shell (assuming you are on Unix/Linux) there will be created a directory named dump. And under dump is a directory named after the example database dave.

A dave.bson file was created with the data.

BSON to Text

Executing bsondump dave.bson > output.file will convert the MongoDB BSON formatted data into something much easier to work with.

$ cat output.file 
{"_id":123.0,"name":"Dave"}
{"_id":456.0,"name":"Jack"}
{"_id":789.0,"name":"Dexter"}
{"_id":787.0,"name":"Boo"}

The output.file can be processed in a number of ways to pull it into MySQL such as using your favorite text editor to wrap insert statements around the data or using a simple program that reads a line from the text file and then send data to the database.

MySQL Document Store -- The NoSQL Zipcodes

The MySQL Document Store functionality allows developers to use a relation database with or without SQL (structured Query Language), also known as NoSQL. The example in this blog is hopefully a simple look at this new feature of MySQL. The example data used is from JSONStudio.com and is a JSON formatted data set for US zip (postal) codes (656K compressed). So download your copy of this data set and lets get to work.

Create a collection

Collections are tables and below we create a collection name 'zip' in the test database in the Python dialect.

mysqlsh -u root -p --py test
Creating an X Session to root@localhost:33060/test
Enter password:
Default schema `test` accessible through db.

Welcome to MySQL Shell 1.0.4 Development Preview

Copyright (c) 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type '\help', '\h' or '\?' for help.

Currently in Python mode. Use \sql to switch to SQL mode and execute queries.
mysql-py> db.createCollection("zip")

Is it there?

As soon as most of use create a table we want to see if it is there.

mysql-py> db.getCollections();
[
    <Collection:zip>
]
mysql-py> 

So it is there. But what is the underlying structure of this table. Switch to SQL dialect (or open a mysql client.

mysql> SHOW CREATE TABLE zip;
+-------+--------------------------------+
| Table | Create Table                                                                                                                                                                                                       |
+-------+--------------------------------+
| zip   | CREATE TABLE `zip` (
  `doc` json DEFAULT NULL,
  `_id` varchar(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,'$._id'))) STORED NOT NULL,
  PRIMARY KEY (`_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+--------------------------------+
1 row in set (0.00 sec)

mysql> 

If you peeked at the zip code file you downloaded, you may have noticed that it has an _id field already. But what if your data set has no _id or you want to use another key/value pair from the data as an index? Simply use a stored generated column on the field of your choice. Remember good indexing practices still count as the underlying relational database still has to keep the infrastructure underneath up to date.

Loading data

I will skip over the loading of the zip code data (I can address that in a later blog post if there is any interest. For now lets take it as a given that the data has been moved into the new collection.

Finding a Rainbow

So lets look for a particular zip code. For out data set the zip code corresponds with _id field.And remember that this column is a generated column using that field from the JSON document.

mysql-py> db.zip.find("_id = '76077'")
[
    {
        "_id": "76077",
        "city": "RAINBOW",
        "loc": [
            -97.70652,
            32.281216
        ],
        "pop": 722,
        "state": "TX"
    }
]
1 document in set (0.00 sec)

mysql-py> 

How About Searching a Non-indexed JSON data

Lets look for the state of Texas, or TX in the JSON data. Previous we had the _ID field as a materialized column extracted from the JSON data. Now we are asking the MySQL server to read all the records and return the ones meeting the criteria. This does perform a full table scale of the data (not as efficient as as index) but, thanks to the relatively small amount of records, it does return fairly quickly.

mysql-py> db.zip.find("state = 'TX'")
.
.  (Omitted)

.  
{
        "_id": "79935",
        "city": "EL PASO",
        "loc": [
            -106.330258,
            31.771847
        ],
        "pop": 20465,
        "state": "TX"
    },
    {
        "_id": "79936",
        "city": "EL PASO",
        "loc": [
            -106.30159,
            31.767655
        ],
        "pop": 52031,
        "state": "TX"
    }
]
1676 documents in set (0.06 sec)

mysql-py> 

Wrap Up

So now we can create a collection and search it. But what happens when we add records and especially records without our index-able key? That will be covered in another blog soon.