Not a Fan of Redhat RPMs Today or Why No Follow Up for RH/Centos/Fedora from Last Blog

I received a lot of good feedback Building the PHP MySQL XDevAPI PECL Extension on MySQL 8.0.11 and PHP 7.2 for the MySQL Document Store including a few folks asking if I could document that it takes to get the MySQL X DevAPI working with an RPM based Linux distro.

Well I'd really like to.  But I can't.

Redhat Linux 4

I still remember getting my copy of Redhat Linux 4.0 (not RHEL -- no enterprise thoughts in those days)  It was January 1997 and I installed it the week before Rehaht 4.1 came out.  I thought that RPMs were much better than the old 'unzip the tar file;./configure; make install' circus. I thought Redhat was pretty cool. Heck I even became a RHCE.  

Then I found the Debian variants easier to work with and more up to date.  My not so humble opinion is that Ubuntu is the best all around Linux distro around. But there are a lot of RPM based systems out there and I need to be able to show how to get the MySQL X Devapi working on them.  But it ain't easy.

Step 1 Install CentOS 7

I had not installed CentOS in some time and it installed fairly nicely or as nice as Ubuntu.   So no problem there,

Step 2 What is that Deb package Called in RPM speak?

Here is where the wagon goes off the rails. TO get the developer tools you must sudo yum -y groupinstall  'Development Tools'.  Then you get to download the OpenSSL tarball 'cause there is not one RPM for CentOS 7 (considering its popularity I was gobsmacked to discover this lacking). Next was loading the protobuf-devel package.  

But when I try to run the PECL install mysql_xdevapi I run into a compiler error.  Well, it is Friday afternoon and my frustration/inexperience with recent RPM software is telling me to call it a week.

I will try again later.  If you are more up to speed on RPMs and want to provide guidance for me please do so.  If not have a good weekend!

Building the PHP MySQL XDevAPI PECL Extension on MySQL 8.0.11 and PHP 7.2 for the MySQL Document Store

The MySQL Document Store is a NoSQL JSON document store built upon well known MySQL database technology.  PHP runs about eight percent of the Internet.  So putting the two together is a big priority for me. So this blog post is about getting all this together on a Ubuntu 18.04 system.

Note that I will be teaching PHP and the X DevAPI at Oracle Code One and hopefully in some tutorials/workshops this year.  These session will feature the X DevAPI installed on Virtual Box images and I probably will not have time to cover these steps in detail but I will point to this as reference material.

PHP 7.2 

PHP's performance has really skyrocketed with the seven series and the newer betas are looking very impressive.  But to use the new X Devapi you will need to get the shared object for it into your PHP server. 

The MySQL X DevAPI PECL Extension

You can find the MySQL X DevAPI among the many PECL extensions and you can get the latest tarball of source code and also a link to the homepage.  And on that home page are directions for installing/configure the extension. The docs say to do the followings and assume you already have MySQL 8.0.11 installed (or go to https://dev.mysql.com/downloads for the MySQL apt repo software; Install it and then run  sudo apt-get install mysql-shell mysql-server).

$ apt install build-essential libprotobuf-dev libboost-dev openssl protobuf-compiler
$ add-apt-repository ppa:ondrej/php
$ apt install php7.2-cli php7.2-dev php7.2-mysql php7.2-pdo php7.2-xml
$ pecl install mysql_xdevapi

And a quick program to make sure PHP could use the X Devapi.

<?php

$session = mysql_xdevapi\getSession("mysqlx://root:oracle@localhost:33060");
if ($session === NULL ) {
  die("Connection not established!\n");
}

echo "Connection established!\n");

?>

Pretty simple, eh?  Well, I had problems. A call to an undefined function mysql_xdevapi\getSession error.  For some reason the X DevAPI shared object was not being found. 

A Fix

Now there is a way to get things to work but it takes a little work.
1. cd /etc/php/7.2/mods-available
2. cp mysqli.ini mysql_xdevapi.ini
3. edit mysql_xdevapi.ini and change mysqli to mysql_xdevapi on the last line.
4. cd /etc/php/7.2/cli/conf.d
5. ln -s /etc/php/7.2/mods-available/mysql_xdevapi.ini 20-mysql_xdevapi.ini

Now the first test program runs and the Connection established message is displayed!

A Bigger Test

Here is a bigger test program:

 #!/bin/php
<?php

$session = mysql_xdevapi\getSession("mysqlx://root:hidave@localhost:33060");
if ($session === NULL) {
  die("Connection could not be established");
}

$dave = [
  "name" => "Dave",
  "state"  => "TX",
  "category" => 1,
  "job"  => "Community Manager"
];
$alex = [
  "name" => "Alex",
  "age"  => 28,
  "category" => 2,
  "job"  => "House Flipper"
];

$schema = $session->getSchema("test");
$collection = $schema->createCollection("stuff");
$collection = $schema->getCollection("stuff");

$collection->add($alex, $dave)->execute();
var_dump($collection->find("name = 'Dave'")->execute()->fetchOne());
?>

So now we have a working PHP 7.2 with the MySQL XDevAPI PECL extension.  Later we will look into more uses.

MongoDB versus MySQL Document Store Command Comparisons III

This time we will look at the differences in updating records between MongoDB and the MySQL Document Store.  Syntactically they are pretty different.  I am still following the Getting Started With MongoDB article for example queries.

Updating Records

In Mongo we update thusly:
> db.restaurants.update(
... { "name" : "Juni" },
... {
...  $set: { "cuisine" : "American (new)" },
...  $currentDate: { "lastModified" : true }
... }
... )
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
>

The same update in the MySQL Document Store can be a lot different.  We could update using SQL or NoSQL.  I would like to update the document with the change to the cuisine and set the last modified to the current data.  The first change is pretty simple:

db.restaurants.modify("name = 'Juni'").set("cuisine","American (new)")



 But what about that last modified value? Well, that on the SQL side would the values of now() but the MySQL NoSQL side does not have that concept, at least it is not documented.  And lastModified is not in the document itself?!?!  Is it metadata hiding somewhere and hiding somewhere in the Mongo documentation??

Well, after some frustration with searching for document metadata and timestamps with Mongo documents, I decided to circle back to this later.

Updating Embedded Fields

The updating of embedding fields can be as mess but both products can handle  this operation.  The Mongo version is:

> db.restaurants.update(
... { "restaurant_id" : "41156888" },
... { $set: { "address.street": "East 31st Street" }}
... )
And the MySQL Document Store's version:

db.restaurants.modify("restaurant_id ='41156888'").set("address.street","East 31st Street")

So both do what is wanted but have much different syntax.

Updating Multiple Documents

Changing multiple records can be tricky, especially for novices or those learning new software.  Usually it is best to run the SQL version of the old  MySQL shell in --i-am-a-dummy mode for novices where forgetting a WHERE clause can be disastrous.  But the new MySQL shell does not have this option and Mongo forces you to expressly state you want to change multiple records.  Here is a distinction between the two products:


> db.restaurants.update( 
   { "address.zipcode" : "10016", "cuisine" : "Other"}, 
   { $set: { "cuisine" : "Category to be determined"}},
   { multi: true }
 )
WriteResult({ "nMatched" : 20, "nUpserted" : 0, "nModified" : 20 })
> 

Mongo will update only one record unless multi is set to true.  If not set you will get only one record updated. 

MySQL has no such limitation and will change multiple records. 

db.restaurants.modify('address.zipcode = "10016" and cuisine = "Other"').set('cuisine','TBD')
Query OK, 20 items affected (0.2997 sec)

I am also a fan of the explicit and in the MySQL query and not much of a fan of the implied and in the Mongo query. Why? When you try to debug things at two in the morning it is very easy to assume an or or other comparison operator.  When you program assembler you get picky about things like or, xor, and ands.

Picking Output Keys

You may not want all the keys and values from a document every time you dive into the data. Specifying specific keys again shows the differences in syntax between the two products

Mongo:
 > db.restaurants.find( { "name" : "Pizza Plus" }, 
   { name : 1 , borough : 1})
{ "_id" : ObjectId("5b2293b4f46382c40db8264f"), "borough" : "Brooklyn", "name" : "Pizza Plus" }
{ "_id" : ObjectId("5b2293b6f46382c40db86fb0"), "borough" : "Manhattan", "name" : "Pizza Plus" }
{ "_id" : ObjectId("5b2293b6f46382c40db8854b"), "borough" : "Brooklyn", "name" : "Pizza Plus" }
>

You get the _id if you want it or not. MySQL only gives you the desired values for the specified keys.

MySQL:

db.restaurants.find('name = "Pizza Plus"').fields('name','borough')
[
    {
        "borough": "Brooklyn",
        "name": "Pizza Plus"
    },
    {
        "borough": "Manhattan",
        "name": "Pizza Plus"
    },
    {
        "borough": "Brooklyn",
        "name": "Pizza Plus"
    }
]
3 documents in set (0.0486 sec)


So those extra characters are not so bothersome with small data sets but when you have millions of lines or more that extra really add overhead.

 

MonoDB versus MySQL Document Store Command Comparisons II

Last time I was stumped by the MongoDB $gt: operator.  I wanted to look for restaurants in a certain Manhattan burough OR in a zipcode greater than a certain zipcode.  Well, I was getting different results between Mongo and MySQL.

To > or Not To >, That Is the Query

Lets say we have three records with the same key but the values are 1, 2, and "3". Yup, you got it two numerics and one string.  I would expect schema less data to be free flowing, not typed, and pretty much a free for all.  Whoops. Bad assumption on my part for Mongo use.

I added three JSON documents into Mongo as can be seen below:

Our three documents with the values of 1, 2, & "3" in Mongo

And the same data into the MySQL Document Store:

Our test data in the MySQL Document Store with the values of 1, 2, and "3"

The Search

 Now search for items with a value greater than 1. MySQL handles this by returning two records:

MySQL says there are two documents where the value of item is greater than 1

However Mongo differs:

Mongo says ther is only one document with the value of item great than 1

What Happened?

Mongo's reference manual under db.collection.find informs us that 'comparison operators only perform comparisons on documents where the BSON type of the target field matches the type of the query operand.' and silly me was trying to compare a numeric to a string.

Now you can enforce data types in a Mongo collection by using schema validation.  But I thought the beauty of schema less data was that you did not have to normalize the data.

Am I picking nits? Well, I have been pushing data around in computers for close to four decades now and wish I had a nickel (or other small coin of similar value in another curency) for every time I had to 'wash' my data to switch it from Type X to Type Y or reshuffle dates or even add/remove thousands separators.  To me schema less means that "3" and 3 are equal. At least until they are cast to a type.

Next Time

I have been getting some very good feedback on this subject and will delve into this more.

MongoDB versus MySQL Document Store command comparisons I

Both MongoDB and the MySQL Document Store are JSON document stores.  The syntax differences in the two products are very interesting.  This long will be a comparison of how commands differ between these two products and may evolve into a 'cheat sheet' if there is demand.

I found an excellent Mongo tutorial Getting Started With MongoDB that I use as a framework to explore these two JSON document stores.

The Data

I am using the primer-dataset.json file that MongoDB has been using for years  in their documentation, classes, and examples. MySQL has created the world_x data set based on the world database used for years in documentation, classes and examples.  The data set is a collection of JSON documents filled with restaurants around Manhattan.

For the Mongo examples the schema name is test and the collection is named restaurants while the MySQL corollary schema name is nyeats and the collection is named restaurants.  I kept the collection names the same between the two products and hope that the differences in schema names causes no problems. Please see my previous entry if you seek details on loading this data into the MySQL Document Store.

Starting the Shells

The first step in comparing how the two work is access the data through their respective shells.  

The MySQL mysqlsh connected to the nyeats schema

The MongoDB mongo shell connected to the test schema

 I have widows with both shells ready to go and not it is time to start the comparison.

All The Records in a Collection

Both use db as a global variable to point to the current schema. Simply typing db at the command prompt will report back the current active schema for both.

But what if you want to see all the records in the collection restaurants?  With both you can issue db.restaurants.find() but where MySQL returns all the documents in the collection Mongo has a pager that requires you to type 'it' to continue?

Find Documents by Cuisine

So lets pick restaurants by their cuisine and since Red Beans and Rice is one of my favorites we will use Cajun as the cuisine of choice.  The arguments to the find() function are a JSON object in Mongo and an equation for MySQL.

MySQL:  db.restaurants.find("cuisine = 'Cajun'")
Mongo:   db.restaurants.find( { "cuisine" : "Cajun" })

The output is show below under 'Output From Cajun Cuisine as it takes up a lot of real estate on a computer screen.  The big difference for those who do not want to page down is that MySQL pretty prints the output while Mongo does not. The pretty print is much easier on my old eyes.

Restaurants By Zipcode

 How about we look for restaurants in one Zipcode (or postal code for those outside the USA). By the way a Zipcode can cover a lot of territory.

Mongo takes a JSON object as the search parameter while MySQL wants and equation.  Note that we are using a second tier key 'address.zipcode' to reach the desired information.

MySQL:  db.restaurants.find("address.zipcode = '10075'")
MongoDB:  db.restaurants.find( { "address.zipcode": "10075" })

When gt Is Not Great Than >!!!

I wanted to tinker with the above by changing the equal sign to a great than. It is easy to change the equal sign in the MySQL argument to any other relation symbol like <, >, or >= intuitively.  I am still working on getting Mongo's $gt to work (Not intuitive or easy).

Logical OR

So far there has not been a whole lot of difference between the two. But now we start to see differences. The or operator for Mongo wants a JSON array with the delimiters inside JSON objects.  MySQL looks more like traditional SQL.

MongoDB: db.restaurants.find( 
     { $or : [ { "cuisine": "Cajun"}, { "address.zipcode": "10075" } ] } ) 
MySQL:  db.restaurants.find(
     "cuisine = 'Cajun' OR address.zipcode = '10075'")

To me the MySQL argument looks more like every other programming language I am used to.  

Sorting on Two Keys

Let sort the restaurants by burough and zipcode, both ascending.   Mongo is looking for JSON objects with the key name and sort order (1 for ascending, -1 for descending!) while MySQL defaults to ascending on the keys provided.

MongoDB: db.restaurants.find().sort( { "burough" : 1, "address.zipcode" : 1 })
MySQL:     db.restaurants.find().sort("burough","address.zipcode")

End of Part I

I am going to spend some time to dive deeper into the differences between the two and especially Mongo's confusing (at least to me) great than expression.

Output From Cajun Cuisine

MySQL:
JS > db.restaurants.find("cuisine = 'Cajun'")
[
    {
        "_id": "00005b2176ae00000000000010ec",
        "address": {
            "building": "1072",
            "coord": [
                -74.0683798,
                40.6168076
            ],
            "street": "Bay Street",
            "zipcode": "10305"
        },
        "borough": "Staten Island",
        "cuisine": "Cajun",
        "grades": [
            {
                "date": {
                    "$date": 1408579200000
                },
                "grade": "A",
                "score": 13
            },
            {
                "date": {
                    "$date": 1391644800000
                },
                "grade": "A",
                "score": 9
            },
            {
                "date": {
                    "$date": 1375142400000
                },
                "grade": "A",
                "score": 12
            },
            {
                "date": {
                    "$date": 1338336000000
                },
                "grade": "A",
                "score": 8
            }
        ],
        "name": "Bayou",
        "restaurant_id": "40974392"
    },
    {
        "_id": "00005b2176ae000000000000128a",
        "address": {
            "building": "9015",
            "coord": [
                -73.8706606,
                40.7342757
            ],
            "street": "Queens Boulevard",
            "zipcode": "11373"
        },
        "borough": "Queens",
        "cuisine": "Cajun",
        "grades": [
            {
                "date": {
                    "$date": 1420848000000
                },
                "grade": "A",
                "score": 11
            },
            {
                "date": {
                    "$date": 1400457600000
                },
                "grade": "A",
                "score": 7
            },
            {
                "date": {
                    "$date": 1384473600000
                },
                "grade": "A",
                "score": 12
            },
            {
                "date": {
                    "$date": 1370390400000
                },
                "grade": "B",
                "score": 16
            },
            {
                "date": {
                    "$date": 1338249600000
                },
                "grade": "A",
                "score": 7
            }
        ],
        "name": "Big Easy Cajun",
        "restaurant_id": "41017839"
    },
    {
        "_id": "00005b2176ae0000000000002146",
        "address": {
            "building": "90-40",
            "coord": [
                -73.7997187,
                40.7042655
            ],
            "street": "160 Street",
            "zipcode": "11432"
        },
        "borough": "Queens",
        "cuisine": "Cajun",
        "grades": [
            {
                "date": {
                    "$date": 1416873600000
                },
                "grade": "A",
                "score": 9
            },
            {
                "date": {
                    "$date": 1384732800000
                },
                "grade": "A",
                "score": 10
            },
            {
                "date": {
                    "$date": 1366070400000
                },
                "grade": "B",
                "score": 16
            },
            {
                "date": {
                    "$date": 1345507200000
                },
                "grade": "B",
                "score": 18
            }
        ],
        "name": "G & L Cajun Grill",
        "restaurant_id": "41336510"
    },
    {
        "_id": "00005b2176ae0000000000002ce7",
        "address": {
            "building": "2655",
            "coord": [
                -74.1660553,
                40.5823983
            ],
            "street": "Richmond Avenue",
            "zipcode": "10314"
        },
        "borough": "Staten Island",
        "cuisine": "Cajun",
        "grades": [
            {
                "date": {
                    "$date": 1412035200000
                },
                "grade": "A",
                "score": 10
            },
            {
                "date": {
                    "$date": 1392768000000
                },
                "grade": "B",
                "score": 18
            },
            {
                "date": {
                    "$date": 1371772800000
                },
                "grade": "B",
                "score": 16
            },
            {
                "date": {
                    "$date": 1335916800000
                },
                "grade": "A",
                "score": 11
            },
            {
                "date": {
                    "$date": 1322611200000
                },
                "grade": "A",
                "score": 11
            }
        ],
        "name": "Cajun Cafe & Grill",
        "restaurant_id": "41485811"
    },
    {
        "_id": "00005b2176ae000000000000352d",
        "address": {
            "building": "509",
            "coord": [
                -73.964513,
                40.693846
            ],
            "street": "Myrtle Avenue",
            "zipcode": "11205"
        },
        "borough": "Brooklyn",
        "cuisine": "Cajun",
        "grades": [
            {
                "date": {
                    "$date": 1417651200000
                },
                "grade": "A",
                "score": 13
            },
            {
                "date": {
                    "$date": 1386028800000
                },
                "grade": "A",
                "score": 9
            },
            {
                "date": {
                    "$date": 1370390400000
                },
                "grade": "A",
                "score": 4
            },
            {
                "date": {
                    "$date": 1355529600000
                },
                "grade": "A",
                "score": 13
            }
        ],
        "name": "Soco Restaurant",
        "restaurant_id": "41585575"
    },
    {
        "_id": "00005b2176ae0000000000003579",
        "address": {
            "building": "36-18",
            "coord": [
                -73.916912,
                40.764514
            ],
            "street": "30 Avenue",
            "zipcode": "11103"
        },
        "borough": "Queens",
        "cuisine": "Cajun",
        "grades": [
            {
                "date": {
                    "$date": 1418256000000
                },
                "grade": "A",
                "score": 10
            },
            {
                "date": {
                    "$date": 1394668800000
                },
                "grade": "A",
                "score": 0
            },
            {
                "date": {
                    "$date": 1375488000000
                },
                "grade": "B",
                "score": 17
            },
            {
                "date": {
                    "$date": 1358467200000
                },
                "grade": "A",
                "score": 10
            },
            {
                "date": {
                    "$date": 1341446400000
                },
                "grade": "A",
                "score": 12
            },
            {
                "date": {
                    "$date": 1324080000000
                },
                "grade": "A",
                "score": 10
            }
        ],
        "name": "Sugar Freak",
        "restaurant_id": "41589054"
    },
    {
        "_id": "00005b2176ae0000000000004172",
        "address": {
            "building": "1433",
            "coord": [
                -73.9535815,
                40.6741202
            ],
            "street": "Bedford Avenue",
            "zipcode": "11216"
        },
        "borough": "Brooklyn",
        "cuisine": "Cajun",
        "grades": [
            {
                "date": {
                    "$date": 1397001600000
                },
                "grade": "A",
                "score": 8
            },
            {
                "date": {
                    "$date": 1365033600000
                },
                "grade": "A",
                "score": 10
            }
        ],
        "name": "Catfish",
        "restaurant_id": "41685267"
    }
]
7 documents in set (0.0488 sec)



Mongo:

db.restaurants.find( { "cuisine" : "Cajun" })
{ "_id" : ObjectId("5b2293b5f46382c40db834ce"), "address" : { "building" : "1072", "coord" : [ -74.0683798, 40.6168076 ], "street" : "Bay Street", "zipcode" : "10305" }, "borough" : "Staten Island", "cuisine" : "Cajun", "grades" : [ { "date" : ISODate("2014-08-21T00:00:00Z"), "grade" : "A", "score" : 13 }, { "date" : ISODate("2014-02-06T00:00:00Z"), "grade" : "A", "score" : 9 }, { "date" : ISODate("2013-07-30T00:00:00Z"), "grade" : "A", "score" : 12 }, { "date" : ISODate("2012-05-30T00:00:00Z"), "grade" : "A", "score" : 8 } ], "name" : "Bayou", "restaurant_id" : "40974392" }
{ "_id" : ObjectId("5b2293b5f46382c40db8366b"), "address" : { "building" : "9015", "coord" : [ -73.8706606, 40.7342757 ], "street" : "Queens Boulevard", "zipcode" : "11373" }, "borough" : "Queens", "cuisine" : "Cajun", "grades" : [ { "date" : ISODate("2015-01-10T00:00:00Z"), "grade" : "A", "score" : 11 }, { "date" : ISODate("2014-05-19T00:00:00Z"), "grade" : "A", "score" : 7 }, { "date" : ISODate("2013-11-15T00:00:00Z"), "grade" : "A", "score" : 12 }, { "date" : ISODate("2013-06-05T00:00:00Z"), "grade" : "B", "score" : 16 }, { "date" : ISODate("2012-05-29T00:00:00Z"), "grade" : "A", "score" : 7 } ], "name" : "Big Easy Cajun", "restaurant_id" : "41017839" }
{ "_id" : ObjectId("5b2293b5f46382c40db84528"), "address" : { "building" : "90-40", "coord" : [ -73.7997187, 40.7042655 ], "street" : "160 Street", "zipcode" : "11432" }, "borough" : "Queens", "cuisine" : "Cajun", "grades" : [ { "date" : ISODate("2014-11-25T00:00:00Z"), "grade" : "A", "score" : 9 }, { "date" : ISODate("2013-11-18T00:00:00Z"), "grade" : "A", "score" : 10 }, { "date" : ISODate("2013-04-16T00:00:00Z"), "grade" : "B", "score" : 16 }, { "date" : ISODate("2012-08-21T00:00:00Z"), "grade" : "B", "score" : 18 } ], "name" : "G & L Cajun Grill", "restaurant_id" : "41336510" }
{ "_id" : ObjectId("5b2293b5f46382c40db850c6"), "address" : { "building" : "2655", "coord" : [ -74.1660553, 40.5823983 ], "street" : "Richmond Avenue", "zipcode" : "10314" }, "borough" : "Staten Island", "cuisine" : "Cajun", "grades" : [ { "date" : ISODate("2014-09-30T00:00:00Z"), "grade" : "A", "score" : 10 }, { "date" : ISODate("2014-02-19T00:00:00Z"), "grade" : "B", "score" : 18 }, { "date" : ISODate("2013-06-21T00:00:00Z"), "grade" : "B", "score" : 16 }, { "date" : ISODate("2012-05-02T00:00:00Z"), "grade" : "A", "score" : 11 }, { "date" : ISODate("2011-11-30T00:00:00Z"), "grade" : "A", "score" : 11 } ], "name" : "Cajun Cafe & Grill", "restaurant_id" : "41485811" }
{ "_id" : ObjectId("5b2293b5f46382c40db8590d"), "address" : { "building" : "509", "coord" : [ -73.964513, 40.693846 ], "street" : "Myrtle Avenue", "zipcode" : "11205" }, "borough" : "Brooklyn", "cuisine" : "Cajun", "grades" : [ { "date" : ISODate("2014-12-04T00:00:00Z"), "grade" : "A", "score" : 13 }, { "date" : ISODate("2013-12-03T00:00:00Z"), "grade" : "A", "score" : 9 }, { "date" : ISODate("2013-06-05T00:00:00Z"), "grade" : "A", "score" : 4 }, { "date" : ISODate("2012-12-15T00:00:00Z"), "grade" : "A", "score" : 13 } ], "name" : "Soco Restaurant", "restaurant_id" : "41585575" }
{ "_id" : ObjectId("5b2293b5f46382c40db8596b"), "address" : { "building" : "36-18", "coord" : [ -73.916912, 40.764514 ], "street" : "30 Avenue", "zipcode" : "11103" }, "borough" : "Queens", "cuisine" : "Cajun", "grades" : [ { "date" : ISODate("2014-12-11T00:00:00Z"), "grade" : "A", "score" : 10 }, { "date" : ISODate("2014-03-13T00:00:00Z"), "grade" : "A", "score" : 0 }, { "date" : ISODate("2013-08-03T00:00:00Z"), "grade" : "B", "score" : 17 }, { "date" : ISODate("2013-01-18T00:00:00Z"), "grade" : "A", "score" : 10 }, { "date" : ISODate("2012-07-05T00:00:00Z"), "grade" : "A", "score" : 12 }, { "date" : ISODate("2011-12-17T00:00:00Z"), "grade" : "A", "score" : 10 } ], "name" : "Sugar Freak", "restaurant_id" : "41589054" }
{ "_id" : ObjectId("5b2293b6f46382c40db86551"), "address" : { "building" : "1433", "coord" : [ -73.9535815, 40.6741202 ], "street" : "Bedford Avenue", "zipcode" : "11216" }, "borough" : "Brooklyn", "cuisine" : "Cajun", "grades" : [ { "date" : ISODate("2014-04-09T00:00:00Z"), "grade" : "A", "score" : 8 }, { "date" : ISODate("2013-04-04T00:00:00Z"), "grade" : "A", "score" : 10 } ], "name" : "Catfish", "restaurant_id" : "41685267" }

More Porting Data from MongoDB to the MySQL Document Store

Last time we looked at moving a JSON data set from MongoDB to the MySQL Document Store.  Let's move another and then see how to investigate this date.  We will use the primer-dataset.json that contains data on restaurants around New York City.

Loading Data

The loading of the JSON data set was covered last time but here is the gist. The first step is to fire up the MySQL Shell and login to the server.

Here a new schema is created and then a new collection

 We need a new schema for this data and the example shows one created as nyeats.  The within that new schema a collection is created with the name restaurants.

Then we switch to Python mode to load the data with a simple program

Switching to Python mode, a simple program reads the data from a file and loads the collection.

What types of Restaurants

We can quickly look at all the restaurants but it may be easier to start out looking at the types of cuisine. And it would be nice to see the numbers of each type.

The result set:

[
    {
        "$.cuisine": "Polynesian",
        "count('*')": 1
    },
    {
        "$.cuisine": "Café/Coffee/Tea",
        "count('*')": 2
    },
    {
        "$.cuisine": "Cajun",
        "count('*')": 7
    },
...
    {
        "$.cuisine": "Latin (Cuban, Dominican, Puerto Rican, South & Central American)",
        "count('*')": 850
    },
    {
        "$.cuisine": "Other",
        "count('*')": 1011
    }
]
85 documents in set (0.7823 sec)

The big surprise for me was the 1,011 other restaurants after seeing a rather inclusive list of cuisine styles.

What cuisine types are available and their numbers?

Feel like Red Beans and Rice

So lets narrow our search down and look for some Cajun food.  But since we are health conscious we will want to check the health department ratings on the restaurants.

And we can see the names of the restaurants with their latest health department grades.

Next time we will dig deeper into our NYC restaurants

Porting Data From MongoDB to MySQL Document Store in TWO Easy Steps

Porting data from MongoDB to the MySQL Document Store is very easy.  The example I will use is an example data set from the good folks at Mongo named zips.json that contains a list of US Postal Codes and can be found at http://media.mongodb.org/zips.json for your downloading pleasure.

I copied the file into the Downloads directory on my Unbuntu laptop and then fired up the new MySQL Shell.  After login, I created a new schema creatively named zips with session.createSchema('zips').  When then set the db object to this new schema with the command \use zips.

Creating a new schema named 'zips' and then informing the system that I wish to use this new schema as the db object

Now it is time to populate the schema with a collection for holding documents. The collection is named zip and is created with db.createCollection('zip') The next step is to read the zips.json file into the collection using Python

We need to create a new collection named zip in the schema we just created and then switch to Python mode to read in the data line by line and store it as a document in the zip collection.

You might want to go back and read the wonderful presentation and scripts by Giuseppe Maxia on loading MongoDB data into MySQL at https://github.com/datacharmer/mysql-document-store as he originated this very useful bit of code.

One thing that helps is that this data set has a _id field that the MySQL Document Store will grab and use as the InnoDB primary key.  I will have to brush up on my Python to extract another value from data sets to use for the _id field.


And we can now perform NoSQL searches on the data.

A NoSQL search of the data in the zip collection

Or SQL. First we have to change to SQL node with \sql and then search for the same record with SELECT * FROM zip WHERE _id='01010';

A SQL search of the data in the zip table

If you have questions about porting data from MongoDB into the MySQL Document Store or the Document Store in general, please drop me a line.

Zero to DBA Hero Track at Southeast Linuxfest

This year the Zero to DBA Hero track at the Southeas  Llinuxfest expands to a second day.  The event is free to attend but it helps if you pre register.  Here is the agenda of that you will see in Charlotte June 8th, 9th, and 10th.

MySQL Talks at SELF – Zero to DBA Hero Track Linode Ballroom

Friday

9am 20 years of MySQL, 20 years of PHP, and 10 Years of SELF -- What the heck has been going on?

Dave Stokes

10:15 Introducing the MySQL Document Store
Charles Bell, PhD

11:30 Performance Analysis and Troubleshooting Methodologies for Databases

Peter Zaitsev

1:30 MySQL Without the SQL -- Oh My!

Dave Stokes

4:00 Introducing MySQL InnoDB Cluster Charles Bell, PhD

Saturday

9am MySQL/Percona Server/MariaDB Server Security Features Overview

Colin Charles

10:15 ZFS and MySQL on Linux, The Sweet Spots

Jervin Real

11:30 The MySQL Ecosystem - Understanding It, Not Running Away From It!!

Colin Charles

1:30 MySQL Troubleshooting and Performance Optimization with PMM

Peter Zaitsev

2:45 MySQL Windowing Functions

Dave Stokes

4:00 Understanding the Licensing of Your Database Stack

Colin Charles

Sunday

9am Copyright, Open Source, the Free Market, and the History of MySQL

Alexander Rubin

MySQL is again proud to be a platinum sponsor and please drop by the MySQL booth to talk about MySQL 8, Group Replication, and the MySQL Document Store,