Wednesday, October 4, 2017
2017 MySQL Community Team Award
The 2017 MySQL Community Team Award was presented last night at the MySQL Community Reception. As part of this event the MySQL Community Team made a presentation to Alex Rubin of Percona for solving a bug that was report over a dozen years before. The initial bug report was files September 12, 2002 MySQL Does Not Make Toast but Alex demonstrated how to solve the problem in Fixing MySQL Bug#2: now MySQL makes toast!
. For this contribution and many years of long, hard work in the MySQL Community, the MySQL Community Team is providing this award to recognize this achievement (and we spent a surprising large percentage of the team budget on this award).
Tuesday, October 3, 2017
MySQL Shell with Command Completion
MySQL Shell
CLI Interfaces are usually boring, ASCII-ish functional interfaces that are as about as exciting as paint drying or end user license agreements for your rice steamer. They get the job done but no excitement. The new MySQL Shell (mysqlsh instead of mysql at the command line is a great new tool but like its predecessor it is not exactly visually dynamic.Until Now.
At labs.mysql.com there is a new version of the MySQL Shell that adds some new functionality and some visual enticements. I was in a session at Oracle OpenWorld and was impressed by not only the visually stunning upgrade but by the fact that we now get command auto-completion!
Security too
So connect to a database and then check your status. And pow! your connected with SSL. I am running the MySQL 8.0.3 RC and the labs release of the shell but I did not set up SSL/TLS, but there is it.
Command Completion
And there is even command completion. Few of us cam remember all the syntax for MySQL Syntax plus all the table or schema info.Take this example. I switch into SQL mode (note the notice about loading the auto-completion information). The first query I got to SELECT * FROM and then hit a tab. Auto-completion wisely asked if I wanted all 752 possibilities. Then I entered SELECT * FROM ci before entering a tab. i was given two options, one for CIPHER and the other for the table city.
Please Test
Please download this prospective new shell from Labs.MySQL.Com, kick the tires, and send feedback.
Sunday, September 24, 2017
MySQL 8's Windowing Function Part 1
MySQL will have Windowing functions and CTEs which will mean it will be easier to do data analysis with MySQL. You can now make calculations on data from each row in a query plus rows related to that row. Windows will make it easier to group items when GROUP BY does not meet needs. This is a great breakthrough but the new documentation has a steep learning curve if you are starting from zero. Hopefully this and following blogs will make it easier to get started with Windowing Functions.
OVER & WINDOW as a keywords
Let's start with the world_x sample database. The sample below orders the city table by the CountryCode but notice the window w as (order by CountryCode) phrase. This sets up a group for analysis, or a window on the data. For this example we will get the row number, rank, and dense rank of the data in that group. So for CountryCode of ABW we get a row number of 1, rank of 1, and dense rank of 1. The dense rank, the last column, increases by one as the CountryCode increases. The rank column increases also but keeps the same number as the rest of the CountryCode group. A GROUP BY would collapse all this information to a single line for each CountryCode which is not as interesting.mysql> select ID, Name, CountryCode, row_number() over w as 'row#', rank() over w as 'rank', dense_rank() over w as 'dr' from city window w as (order by CountryCode) limit 10; +-----+----------------+-------------+------+------+----+ | ID | Name | CountryCode | row# | rank | dr | +-----+----------------+-------------+------+------+----+ | 129 | Oranjestad | ABW | 1 | 1 | 1 | | 1 | Kabul | AFG | 2 | 2 | 2 | | 2 | Qandahar | AFG | 3 | 2 | 2 | | 3 | Herat | AFG | 4 | 2 | 2 | | 4 | Mazar-e-Sharif | AFG | 5 | 2 | 2 | | 56 | Luanda | AGO | 6 | 6 | 3 | | 57 | Huambo | AGO | 7 | 6 | 3 | | 58 | Lobito | AGO | 8 | 6 | 3 | | 59 | Benguela | AGO | 9 | 6 | 3 | | 60 | Namibe | AGO | 10 | 6 | 3 | +-----+----------------+-------------+------+------+----+ 10 rows in set (0.01 sec)
Good Material
Do you know where good material on SQL Windowing functions lurk? Please send it on to me as I am having difficulty finding good novice to intermediate level training materials.
Thursday, August 10, 2017
Handy JSON to MySQL Loading Script
JSON in Flat File to MySQL Database
So how do you load that JSON data file into MySQL. Recently I had this question presented to me and I thought I would share a handy script I use to do such work. For this example I will use the US Zip (postal) codes from JSONAR. Download and unzip the file. The data file is named zips.json and it can not be bread directly into MySQL using the SOURCE command. It needs to have the information wrapped in a more palatable fashion.head zips.json
{ "city" : "AGAWAM", "loc" : [ -72.622739, 42.070206 ], "pop" : 15338, "state" : "MA", "_id" : "01001" }
{ "city" : "CUSHMAN", "loc" : [ -72.51564999999999, 42.377017 ], "pop" : 36963, "state" : "MA", "_id" : "01002" }
{ "city" : "BARRE", "loc" : [ -72.10835400000001, 42.409698 ], "pop" : 4546, "state" : "MA", "_id" : "01005" }
{ "city" : "BELCHERTOWN", "loc" : [ -72.41095300000001, 42.275103 ], "pop" : 10579, "state" : "MA", "_id" : "01007" }
{ "city" : "BLANDFORD", "loc" : [ -72.936114, 42.182949 ], "pop" : 1240, "state" : "MA", "_id" : "01008" }
{ "city" : "BRIMFIELD", "loc" : [ -72.188455, 42.116543 ], "pop" : 3706, "state" : "MA", "_id" : "01010" }
{ "city" : "CHESTER", "loc" : [ -72.988761, 42.279421 ], "pop" : 1688, "state" : "MA", "_id" : "01011" }
{ "city" : "CHESTERFIELD", "loc" : [ -72.833309, 42.38167 ], "pop" : 177, "state" : "MA", "_id" : "01012" }
{ "city" : "CHICOPEE", "loc" : [ -72.607962, 42.162046 ], "pop" : 23396, "state" : "MA", "_id" : "01013" }
{ "city" : "CHICOPEE", "loc" : [ -72.576142, 42.176443 ], "pop" : 31495, "state" : "MA", "_id" : "01020" }
Follow the Document Store Example
The MySQL Document Store is designed for storing JSON data and this example will follow its practices by having a two column table -- a JSON column, and another column for a primary key (remember InnoDB wants so badly to have a primary key on each table that it will create one for you but it is better practice to make it yourself; besides we want to search on the zipcode which is labeled as _id in the data. So we use a stored generated column that uses JSON_UNQUOTE(JSON_EXTRACT(doc,"$_id")) and saves that info in a column named zip.So a simple table is created and it looks like this:
mysql> desc zipcode\g +-------------+-------------+------+-----+---------+-------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------------------+ | doc | json | YES | | NULL | | | zip | char(5) | NO | PRI | NULL | STORED GENERATED | +-------------+-------------+------+-----+---------+-------------------+ 2 rows in set (0.00 sec)
Handy Script
So now we have the data, we have the table, and now we need to convert the data into something MySQL can use to laod the data.Bash is one of those shells with so many rich built-in tools that is hard to remember them all. But it does have a hand read line feature that can be used for the task.
#!/bin/bash
file="/home/dstokes/Downloads/zips.json"
while IFS= read line
do
echo "INSERT INTO zipcode (doc) VALUES ('$line');"
done <"$file"
Run the script and output the data to a file named foo, ./loader.sh > foo.
The output shows how the data is wrapped:
$head foo
INSERT INTO zipcode (doc) VALUES ('{ "city" : "AGAWAM", "loc" : [ -72.622739, 42.070206 ], "pop" : 15338, "state" : "MA", "_id" : "01001" }');
INSERT INTO zipcode (doc) VALUES ('{ "city" : "CUSHMAN", "loc" : [ -72.51564999999999, 42.377017 ], "pop" : 36963, "state" : "MA", "_id" : "01002" }');
INSERT INTO zipcode (doc) VALUES ('{ "city" : "BARRE", "loc" : [ -72.10835400000001, 42.409698 ], "pop" : 4546, "state" : "MA", "_id" : "01005" }');
INSERT INTO zipcode (doc) VALUES ('{ "city" : "BELCHERTOWN", "loc" : [ -72.41095300000001, 42.275103 ], "pop" : 10579, "state" : "MA", "_id" : "01007" }');
INSERT INTO zipcode (doc) VALUES ('{ "city" : "BLANDFORD", "loc" : [ -72.936114, 42.182949 ], "pop" : 1240, "state" : "MA", "_id" : "01008" }');
INSERT INTO zipcode (doc) VALUES ('{ "city" : "BRIMFIELD", "loc" : [ -72.188455, 42.116543 ], "pop" : 3706, "state" : "MA", "_id" : "01010" }');
INSERT INTO zipcode (doc) VALUES ('{ "city" : "CHESTER", "loc" : [ -72.988761, 42.279421 ], "pop" : 1688, "state" : "MA", "_id" : "01011" }');
INSERT INTO zipcode (doc) VALUES ('{ "city" : "CHESTERFIELD", "loc" : [ -72.833309, 42.38167 ], "pop" : 177, "state" : "MA", "_id" : "01012" }');
INSERT INTO zipcode (doc) VALUES ('{ "city" : "CHICOPEE", "loc" : [ -72.607962, 42.162046 ], "pop" : 23396, "state" : "MA", "_id" : "01013" }');
INSERT INTO zipcode (doc) VALUES ('{ "city" : "CHICOPEE", "loc" : [ -72.576142, 42.176443 ], "pop" : 31495, "state" : "MA", "_id" : "01020" }');
So now the data can be loaded with mysql -u itisme test < foo.
Tuesday, July 25, 2017
PHP and MySQL Without the SQL
Embedding Structured Query Language (SQL) within PHP, or other programming languages, has been problematic for some. Mixing two programming languages together is just plainly not aesthetically pleasing. Especially when you have a declarative language (SQL) mixed with a procedural-object oriented language. But now, with the MySQL XDevAPI PECL extension, PHP developers can now stop mixing the two languages together together.
MySQL Document Store
The MySQL Document Store eliminates the heavy burden for SQL skills. It is designed to be a high speed, schema-less data store and is based on the MySQL JSON data type. This gives you roughly a gigabyte of store in a document format to do with as needed. So you do not need to architect you data before hand when you have no idea how it will evolve. No need to normalize your data. Now behind the scenes is a power MySQL database server but you are no longer writing SQL to use it!But Is The Code Ugly?
If you looked at previous editions of this blog then you have seen examples of using the MySQL XDevAPI PECL extension. There is another example below of how to search for the information under various keys in a JSON document. The great news is that the code is all very modern looking PHP with no messy SQL statements thumb-tacked onto the code. This should ongoing support by those with little or no SQL skills.Previous you would have had to stick SELECT JSON_EXTRACT(doc,'Name') AS 'Country', JSON_EXTRACT(doc,geography) as 'Geo', JSON_EXTACT(doc,'geography.Region) FROM world_x WHERE _id = "USA" as a string in the PHP code. If you prefer the -> operator to replace JSON_EXTRACT, the code can be trimmed down to SELECT doc->"$.Name" AS 'Country', doc->"$.geography" AS 'Geo', doc->"$.geography.Region" FROM world_x WHERE _id = "USA".
But the XDevAPI simplifies these queries into $result = $collection->find('_id = "USA"')->fields(['Name as Country','geography as Geo','geography.Region'])->execute();. This is much easier to understand than the previous two queries for most. And this example shows how to chain down the document path as it specifies all of the geography hey's values and also just the data under geography.Region. It also show how to alias columns from the document store to a label of the developers choice.
#!/usr/bin/php
<?PHP
// Connection parameters
$user = 'root';
$passwd = 'hidave';
$host = 'localhost';
$port = '33060';
$connection_uri = 'mysqlx://'.$user.':'.$passwd.'@'.$host.':'.$port;
echo $connection_uri . "\n";
// Connect as a Node Session
$nodeSession = mysql_xdevapi\getNodeSession($connection_uri);
// "USE world_x"
$schema = $nodeSession->getSchema("world_x");
// Specify collection to use
$collection = $schema->getCollection("countryinfo");
// Query the Document Store
$result = $collection->find('_id = "USA"')->fields(['Name as Country','geography as Geo','geography.Region'])->execute();
// Fetch/Display data
$data = $result->fetchAll();
var_dump($data);
?>
And The Output
mysqlx://root:hidave@localhost:33060
array(1) {
[0]=>
array(3) {
["Geo"]=>
array(3) {
["Region"]=>
string(13) "North America"
["Continent"]=>
string(13) "North America"
["SurfaceArea"]=>
int(9363520)
}
["Country"]=>
string(13) "United States"
["geography.Region"]=>
string(13) "North America"
}
}
User Guide
The MySQL Shell User Guide is a great place to start learning how to interactively start using the Document Store.
Tuesday, July 18, 2017
Using find() with the MySQL Document Store
The Video
The find() function for the MySQL Document Store is a very powerful tool and I have just finished a handy introductory video. By the way -- please let me have feed back on the pace, the background music, the CGI special effects (kidding!), and the amount of the content.The Script
For those who want to follow along with the videos, the core examples are below. The first step is to connect to a MySQL server to talk to the world_x schema (Instructions on loading that schema at the first link above).\connect root@localhost/world_x
db is an object to points to the world_x schema. To find the records in the countryinfo collection, use db.countryinfo.find(). But that returns 237 JSON documents, too many! So lets cut it down to one record by qualifying that we only want the record where the _id is equal to USA, db.countryinfo.find(‘_id = “USA”’)
Deeper Level items in the Document
You can reach deeper level items by providing the path to the object such as db.countryinfo.find(‘geography.Continent = “North America”). Be sure to remember Case Sensitivity!!Limits, Offsets, and Specifications
It is very simple to place restrictions on the amount of output by post pending .limit(5).skip(6). And you can specify which parameters meet you specification by using find(‘GNP > 8000000’)Limiting Fields
But what if you do not want all the document but just a few certain fields. Then post pend .fields([“Name”, “GNP”]) to find().And once again you can dig deeper into the document with specifying the path, such as.fields([“Name”, “GNP”, “geography.Continent”]).sort(“GNP”).
Sorting
Yes, you can easily sort the output from find() by adding .sort(“GNP”,”Name”) at the end.More Complex Searches
Of course you can make the find() function perform more complex dives into the data such as db.countryinfo.find(‘GNP> 500000 and IndepYear > 1500”).Parameter Passed Values
And find of parameter passed values will be happy to find they have not been forgotten. db.countryinfo.find(“Name = :country”).bind(“Country”,”Canada”)
Sunday, July 16, 2017
MySQL Document Store Video Series
I am starting a series of videos on the MySQL Document Store. The Document Store allows those who do not know Structured Query Language (SQL) to use a database without having to know the basics of relational databases, set theory, or data normalization. The goal is to have sort 2-3 minute episodes on the various facets of the Document Store including the basics, using various programming languages (Node.JS, PHP, Python), and materializing free form schemaless, NoSQL data into columns for use with SQL.
The first Episode, Introduction, can be found here.
Please provide feedback and let me know if there are subjects you would want covered in the near future.
Subscribe to:
Posts (Atom)