Search, Data and Technology

Life with Regular Expressions!

2011-12-02T18:57:00.001-08:00

Damn this is good!
(Credit: xkcd.com)

TImezone conversion for hourly report

2011-11-14T23:15:00.000-08:00

We recently built a real time hourly report where data is streamed in and summarized through multiple jobs and pushed into reporting tables. The business users wanted the reports as quickly as possible and first phase of the project needed to be implemented in short time. With very limited time from concept to design and implementation, I utilized 3rd party web charting service (www.chart.io) and pushed the highly summarized data to set of tables.

During a final reporting, I needed to convert date and hour in UTC to PST. The table had date (date type) and hour (tinyint) columns. Chart.io uses simple x, y chart (line, bar, etc.) and takes any co-ordinates passed for them and displays them. See below for one of final chart.

X axis is date and hour with format 'YYYY-MM-DD HH'. The following SQL converted the date and hour from corresponding 2 columns with format YYYY-MM-DD and HH respectively in UTC timezone to PST.

SELECT substr(convert_tz(concat(date, ' ', if(length(hour) = 1, concat('0',hour), hour)), 'GMT', 'America/Los_Angeles'), 1, 13) as x,

...

FROM

WHERE ...

GROUP BY x...

ORDER BY x ASC

Since maintaining the order is important. For example '2011-11-11 02' hour is right after '2011-11-11 01' hour and before '2011-11-11 03', single digit hours (1 through 9) are concatenated with "0'. Then convert_tz converted the GMT timezone to PST (America/Los_Angeles) time. There was suggestion to simply use -8.00 in hardcoded timezone difference but will result in incorrect hour during the months day light saving time (summer). Since data is stored for each hour in tables over many months and years, by having the above technique one doesn't need to worry about this issue. The system will automatically and correctly offset the hours in the report. Handling timezone conversion is a not a trivial one and see timezone wiki for different timezones around the world. For timezone conversion to work in MySQL, one should load the appropriate data into tables like this.

The reports/charts generated were very useful to business user and helped them take some quick decisive decisions and help build better user experience and improve sales and rev!

Converting 12 hour time Unix timestamp

2011-11-03T19:25:00.000-07:00

Many a times I need to transform time in AM/PM format to unix timestamp for better performance. The external data coming in has date and time in two separate columns like '2011-01-01' as date and '11:59:59 PM' as time. And some other time, I also need to convert timezones typically from a UTC to PST and to do that check this out.

For example, converting "2011-01-01 11:59:59 PM" into corresponding unix timestamp results in an integer 1293926399.

# Create a temp table...
create table tmp.test_dt (
d date,
t varchar(20)
)engine=InnoDB

# Insert a value to test...
insert into tmp.test_dt
(d, t) values
('2011-01-01', '11:59:59 PM')

# Run the conversion sql...
SELECT
unix_timestamp(concat(cast(d as char), ' ', cast(time_format(str_to_date(t,'%r'),'%T') as char) ) )
FROM tmp.test_dt
# Output: 1293926399

# To test
SELECT from_unixtime(1293926399)
# Output: "2011-01-01 23:59:59"

In the conversion select statement, time_format converts the time (t column) from 11:59:59 PM to 23:59:59 of datetime type which is then cast as char to concatenate with date. After concat the string looks like "2011-01-01 23:59:59" which becomes input to unix_timestamp.

Loading timezones into MySQL or InfiniDB

2011-07-28T18:24:00.000-07:00

If you are running queries or reports that utilize mysql timezone convertion function (CONVERT_TZ) make sure that you have run mysql utility (mysql_tzinfo_to_sql) that loads timezone information from the system into mysql server first. Without having loaded the timezone information one will simply get NULL return value and no message or error. This is expected behavior because the required tables for MySQL already exist but they are not populated!

In many threads and forums I have seen developers asking question on how to convert one timezone to another while all they get is null return value. I myself have bitten by it couple of times when I moved to new MySQL servers where the following steps were not done.

Here I am using Ubuntu which has timezone files under /usr/share/zoneinfo directory. In many flavors of unix including Mac OS you will find it there, if not search for it with find command ;)

Also, note you can load all timezones of the world in one go. The SQL created by this utility is large with more than 142K lines of SQL code! After completing the task the following tables must have been populated.

mysql.time_zone
mysql.time_zone_name
mysql.time_zone_transition
mysql.time_zone_transition_type

There is another related table mysql.time_zone_leap_second which we don't need at this point.

# To load all timezone
shell> mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

# You may see output like:
Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh89' as time zone. Skipping it.
# On my system some specific timezone of Riyadh are not defined.  And you can ignore these warnings.

Restart mysql or infinidb server to bring in the timezones and run a test.

# In case of infinidb idbmysql is just an alias
shell> alias idbmysql
alias idbmysql='/usr/local/Calpont/mysql/bin/mysql --defaults-file=/usr/local/Calpont/mysql/my.cnf -u root'

shell> idbmysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.1.39 MySQL Community / Calpont InfiniDB Community 2.2-2 Final (COSS LA)

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

mysql> SELECT utc_timestamp(), CONVERT_TZ(utc_timestamp(),'UTC','MET');
+---------------------+-----------------------------------------+
| utc_timestamp()     | CONVERT_TZ(utc_timestamp(),'UTC','MET') |
+---------------------+-----------------------------------------+
| 2011-07-29 01:14:40 | 2011-07-29 03:14:40                     |
+---------------------+-----------------------------------------+
1 row in set (0.00 sec)

Linux 20 Years

2011-04-10T21:12:00.000-07:00

It has been while since blogged but couldn't resist having this clip embedded in my blog as a tribute to Linux that made the freeware accessible to many.
Enjoy,
Shiva

MySQL Connection over SSH

2010-10-14T12:27:00.000-07:00

When MySQL server is setup to accept only localhost connection through bind_address in /etc/mysql/mysql.cnf (configuration)

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address          = 127.0.0.1

or you want to connect as "root" to mysql and use GUI clients like Workbench or Toad, etc. you can use SSH tunneling.

In operating systems that provide built-in ssh support it is made little easier. Mac OS is one such OS, which I will use as an example below.

Assuming you have already setup ssh connection, run the following command from the local terminal. If you have not setup the ssh, see this brief introductory ssh article. Or run > man ssh for more details.

prompt>  ssh -L 3307:localhost:3306  user@remote.com

and then let this terminal running. Here I am using ssh -L [bind_address:]port:host:hostport parameter and skipped bind_address which is needed only when you have more than one IP attached (multi-IP) to your local system which is typically not the case.

    3307 - The port that you will use on the other clients that need to connect to remote mysql. For example, Workbench.

    localhost:3306 - The remote login details as if you had connected to it through ssh and connecting to mysql instance running on port 3306. If you had successfully, ssh-ed to remote host at the prompt you would have run > mysql -h localhost -u user_name -P 3306

    user@remote.com - Remote user name and remote host

Now start Workbench and setup new connection string by providing the details as if you are connecting to a mysql instance on your local machine running on port 3307. See figure 1. ssh will automatically tunnels your connection to remote host's mysql! See figure 2. On ending the tunneling setup either my closing the terminal where you were running "ssh -L" command and killing it will disconnect the connection to Workbench.

Figure 1.

Figure 2.

Note: Same technique can be used to for other client applications to connect to any of remote applications/databases over SSH.

Have fun,

Shiva

DW solution - IP mapping to Geo Locations

2010-08-09T12:30:00.000-07:00

In my previous few posts on the blog, I covered how to convert IP addresses to corresponding integers and back. When performing a Data Warehouse (DW) tables join between IP addresses for location analysis joining on integer IP column is much faster than joining on string column. As noted in other posts, this article covers IPv4 but technique could be used for IPv6 addresses on using 2 BIGINT.

For geo location info, you can use MaxMind's Geo-City data that is provided in two entities - one with mapping IP block to a location ID and another one with location ID to city/state/country. It is one to many (1:M) relationship and to find an IP address location one need to find the matching IP block and then map it to city or country. There are posts that describe in detail how to perform it effeciently for a given IP. These techniques will work for an OLTP environment where you pass known IP address from UI or application layer to database and gets its city/country.

Fast MySQL Range Queries on MaxMind GeoIP Tables byAndy Skelton
MaxMind GeoIP Install Setup Tutorial by Bartomedia
Finding a user's country/region based on IP by Shlomi Noach
On efficiently geo-referencing IPs with MaxMind GeoIP and MySQL GIS by Jeremy Cole

Geo-City data has following fields seperated by comma and row terminated by new line (\n):

Location ID
Country Code
State/Region
City Name
Postal Code (US only)
Latitude
Longitude
Metro Code
Area Code

IP Block has:

Start IP Address
End IP Address
Location ID

While loading this dimensional data during ETL process, you should also do the IP address transformation to integer as explained in "Converting an IP address to an integer". And Geo-City dimensional table will also have partition range (the reason for this post) and surrogate key (id). You should also perform other data validation like non-overlapping ranges, valid country codes, etc. which are all typical standard activities of DW ETL process. The time, resource and effort put into cleaning and validating the data before pushing the data into final fact tables will pay well within short period of time. To fix a data issue and reconcile the reports in DW/BI environment always leads to lower ROI.

You can also denormalize the above tables and create a single table. In either case by joining on range of IP address for a given set of IPs will result in the same slow performance.

dim_geo_ip_city_location holds around 5.9million rows with following attributes:

CREATE TABLE dim_geo_ip_city_location (
    location_id int NOT NULL,
    start_ip varchar(16) NOT NULL,
    end_ip  varchar(16) NOT NULL,
    country char(2),
    region varchar(64), 
    city varchar(128),
    postal_code varchar(32),
    latitude decimal(8,4),
    longitude decimal(8,4),
    metro_code varchar(8),
    area_code varchar(8),
    ...
)ENIGNE=...

CREATE TABLE dim_ip_block (
    start_ip_num bigint NOT NULL,
    end_ip_num bigint NOT NULL,
    location_id int NOT NULL,
        ...
)ENGINE=...

In MySQL (5.0) optimizer performs range operation effeciently with a constant meaning that you have given the query an IP address like "WHERE ip_addr between 1230 and 1234", for example, but not with range operations without constants. For more info on using range optimization see mysql documentation and for indexing see "Mastering the Art of (MySQL) Indexing".

I did not find any article providing some insights into doing DW/BI analysis on IP addresses. In a DW environment the business user is interested in slicing and dicing the hundreds of thousands to million of IPs. Just trying to find few thousand IP address locations (See query 1 below) was taking more than an hour when joined between lookup table (a table similar to dim_geo_ip_city_location but without partition column (ip_range_partition) and id) and ip address join table even with appropriate indexes like index on (start_ip_num, end_ip_num) or start_ip_num and end_ip_num.

Query 1:

-- Simple query to select IP block
SELECT IA.ip_num, B.location_id, B.start_ip_num, B.end_ip_num
FROM ip_addresses IA use index(...)
JOIN dim_ip_block B use index(...)
WHERE IA.ip_num between B.start_ip_num and B.end_ip_num

This leads to MySQL performing full scans. And in the case of ip_block table (B) it doesn't use any index. For this to be avoided, I partitioned the dimension (lookup) table into 1000 parts and since this table has ~5.9 Million rows each partition will result in ~5,900 rows in each partition. For more on data set partitions see its wiki. The partitions should be numbered in the ascending order with respect to ascending range of ip blocks (start_ip_num). Then also create a another table (dim_ip_range_partition) with start_ip_num and end_ip_num for this each range basically creating super IP blocks.

CREATE TABLE dim_ip_range_partition (
     partition_id int NOT NULL AUTO_INCREMENT,
     start_ip_num bigint NOT NULL,
     end_ip_num bigint NOT NULL,
     ....
) ENGINE=InnoDB ;

Also, create a primary key on patition_id and index on (start_ip_num, end_ip_num) on this table.
The solution is based on two things that there is no overlapping IP block and IP blocks are ordered (in ascending order).

To populate the above table,

Add id (row_id) column to dim_ip_block
Add partition_id integer column to dim_ip_block
Update id from 1 to n (highest start_ip_num) in ascending order of start_ip_num
Partition "n" ids into P parts (n/P) where n > 1 and 1 < P < n
Assign each part/partition ascending integer value
Now update above table dim_ip_range_partition P_TABLE from dim_ip_block B_TABLE where P_TABLE partition id between 1 and P of B_TABLE and P_TABLE.start_ip_num is lowest B_TABLE.start_ip_num of the corresponding partition and P_TABLE.end_ip_num is highest of corresponding partition. In other words...

         INSERT INTO dim_ip_range_partition
            (partition_id, start_ip_num, end_ip_num, ...)
         SELECT partition_id, min(start_ip_num), max(end_ip_num), ...
         FROM dim_ip_block
         GROUP BY partition_id
         ORDER BY partition_id asc

By doing so, we have created a super IP blocks each having n/P blocks from the original ip_blocks.   And the following query is one way to extract city/country info for given set of IP addresses.

SELECT t.ip_num, t.ip, B.start_ip_num, B.start_ip, B.end_ip_num, B.end_ip
       ,L.city, L.country
FROM 
(
   --- Get the super ip block (partition) 
   SELECT C.ip_num, max(partition_id) partition_id, max(P.start_ip_num) start_ip_num
   FROM ip_addresses C use index(...)
   JOIN dim_ip_range_partition P 
   WHERE C.ip_num >= P.start_ip_num
   GROUP BY C.ip_num
) t
LEFT OUTER JOIN mktg_ip_block B use index(...)  
  ON  t.partition_id = B.partition_id     --- And join on this partition
  AND t.ip_num >= B.start_ip_num 
  AND t.ip_num <= B.end_ip_num
JOIN dim_geo_ip_city_location L          --- Get city/country
  ON B.location_id = L.location_id

The query that would have taken more than hour returns within 30 seconds now! You can further optimize the above design with say more denormalized relations, having a large number of partitions (this is a trade-off, with highest partition n = P, each partition is same as original table which will result in worse off performance!) on both lookup table and lookedup table. Or extending the partitioning concept to more levels (super super block, ....) you can reduce the number of joining rows with little bit more complex query.

The solution is a typical Computer Science way of handling the complex problem by dividing into smaller ones and solving it. In this it is more specific to MySQL solution for large data sets with range operations.

Cheers,
Shiva

Tools to convert IP address

2010-08-01T20:46:00.000-07:00

Following tool converts an ip address to other formats. For more on IP address conversion check out part 1 and 2.

Converting an IP address to an integer and reverse (Part 1)
IP Address conversions (Part 2)

IP address converter:

Enter IP:
	(e.g. 192.168.100.200 or 3232261320 etc)

You can also check out 2's complement of a number here. This will be handy when one needs to check for signed numbers (2's complement).

IP Address conversions

2010-07-30T15:28:00.000-07:00

This is the part 2 of converting an IP address to an integer or vice versa. For more details and little introdution see Part 1.

Converting an ip address to integer and back is little straight forward in MySQL compared to SQL Server since SQL Server (2008) doesn't provide bit shift operators.

In short you can't do,

SELECT 1<<2

nor

SELECT 3232261320 -- IP '192.168.100.200'
* (256*256*256*256*256*256) -- Left shift 6 bytes
/ (256*256*256*256*256*256*256) -- Right shift 7 bytes to get 2nd octet

One will encounter "Arithmetic overflow error converting expression to data type int". Even casting each integer to bigint will not solve the problem as the multiplication results in overflow of signed bigint. And there is no unsigned bigint in SQL Server.

Another way to solve this is to do the following in SQL Server by using bit AND. Retain only the byte that is off interest and then right shift the bits appropriately.

#-- IP '192.168.100.200'
SELECT (cast(3232261320 as bigint) & cast(4278190080 as bigint))/(256*256*256);
SELECT (cast(3232261320 as bigint) & 16711680)/(256*256);
SELECT (cast(3232261320 as bigint) & 65280)/(256);
SELECT (cast(3232261320 as bigint) & 255);

#- And the output will be
192
168
100
200

On systems with unsigned bigint you can also do (MySQL)

SELECT cast(3232261320*256*256*256*256 as unsigned)
/ (256*256*256*256*256*256*256) 4th_octet;

SELECT cast(3232261320*256*256*256*256*256*256*256 as unsigned)
/ (256*256*256*256*256*256*256 as unsigned) as 1st_octet;

They will result in outputs 192 and 200 respectively.

With given ip you can simply run the following to get its integer value,

mysql toad>  
SELECT ( ((substring_index(ip, '.',1))<<24) 
       + ((substring_index(substring_index(ip, '.', 2), '.', -1))<<16)
       + ((substring_index(substring_index(ip, '.', 3), '.', -1))<<8)
       + ((substring_index(substring_index(ip, '.', 4), '.', -1)<<0))
)
FROM
( SELECT '192.168.100.200' as ip
  UNION ALL
  SELECT ip FROM som_ip_table;
) t;

Cheers,
Shiva

Converting an IP address to an integer and reverse

2010-07-28T22:52:00.001-07:00

In dealing with IP address analysis one may required to map IPs to geo location. There are third party applications or software that perform IP to city, state, country mapping and businesses, for example, can make use of them to see where their web traffic is coming from.

Article covers IPv4 addresses which use 32 bits and similar technique can be used to IPv6 addresses which use 128 bits by using 2 BIGINTs to store (64bits each).

It is easy to handle reasonable load when one is querying for few IPs or few hundred IPs either from simple user interface entering one or more IP addresses or through APIs. In these cases IP addresses can simply be matched in string format without much of performance issue but it can become a performance bottleneck when dealing with millions of rows loaded through ETL process into Data Warehouse environment and reports are run against fact tables with hundreds of millions of rows. Under these circumstances converting IP to an integer will boost the performance substantially.

MySQL provides functions to convert IP string to integer and backwards. For example, inet_aton converts string to a number (integer) and inet_ntoa converts number to integer.

inet_aton and inet_ntoa:

mysql> select inet_aton('192.168.100.200');
+------------------------------+
| inet_aton('192.168.100.200') |
+------------------------------+
|             3232261320 |
+------------------------------+

mysql> select inet_ntoa(3232261320);
+-----------------------+
| inet_ntoa(3232261320) |
+-----------------------+
|     192.168.100.200 |
+-----------------------+
1 row in set (0.00 sec)

Bit operations:

The functions are simple to use and at the lower level they basically perform bit shifting. In the above example, '192.168.100.200' is represented as 11000000 10101000 01100100 11001000 in binary form. For more on binary system or to convert a number from decimal to binary see wiki here.

To convert string IP to integer function inet_aton multiplies 192 by 256*256*256, 168 by 258*256, 100 by 256 and then adding 200 (last octet in the above ip address), that is,

and it is same as 192<<24 i.e., left shift 192 by 24 bits, 168 << 16 and 100 << 8. Bit shifting is a much faster multiplication since CPU can use its register to move the bit in simple operation.

In reverse, one can not simply divide the ip integer (3232261320) to get back the ip address in octet string format. But one need to appropriately left and right shift the number (or do multiply and then divide) to get the octet that is of interest. To get the most highest valued position (192), shift the bits to right. For example,

11000000 10101000 01100100 11001000 >> 24 (right shift 24 bits) gives you

00000000 00000000 00000000 11000000

Therefore, all the right most 24 bits dropped off the register (cliff) and we are left with 4th octet 11000000 prefixed with 24 zeros.

mysql> SELECT 3232261320>>24 as 4th_octet;
+-----------+
| 4th_octet |
+-----------+
|     192 |
+-----------+
1 row in set (0.00 sec)

By the same logic to get the 3rd octet, we can move the bits 16 times to the right but we will be left with 4th octet as well. Doing

11000000 10101000 01100100 11001000 >>16 will result in

00000000 00000000 11000000 10101000

So we should first shift the bits to left so that the most highest octet (4th octet) drops off the register. We can do so by shifting left by 8 bits.

mysql> SELECT 3232261320<<8 as 8blsn;  -- 8blsn: 8 bit left shift number
+--------------+
| 8blsn      |
+--------------+
| 827458897920 |
+--------------+
1 row in set (0.00 sec)

Instead of getting a smaller number than before we got a larger number! And obviously, right shifting this number by 16bits to right will not give the 3rd octet of IP. This happens due to fact that MySQL converted int to bigint silently. The 4th octet moved into 5th octet resulting in appending 8 zeros to the right as below.

11000000 10101000 01100100 11001000 00000000

Since bigint is 8 bytes long one need to shift not by 1 byte (8bits) but 5 bytes (40 bits) to delete the highest octet (#192).

Just with 4 byte (32 bit) left shift the register would look like below.

11000000 10101000 01100100 11001000 00000000 00000000 00000000 00000000

And with 40 bit left shift the register would look like thus removing (#192 - 11000000 ),

10101000 01100100 11001000 00000000 00000000 00000000 00000000 00000000

Now right shift 56 bits to get the 3rd octet (#168).

00000000 00000000 00000000 00000000 00000000 00000000 00000000 10101000

This is same as
(3232261320 *256 *256 *256 *256 *256) / (256 *256 *256 *256 *256 *256 *256)

 mysql> SELECT 3232261320><<40>>56;
+--------------------+
| 3232261320<<40>>56 |
+--------------------+
|            168 |
+--------------------+
1 row in set (0.00 sec)

To get the 2nd octet first left shift the original number 48 bits and then right shift by 56 bits. For the 1st octet left shift by 56 bits both ways.

mysql> SELECT 3232261320<<48>>56;
+--------------------+
| 3232261320<<48>>56 |
+--------------------+
|           100 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT 3232261320<<56>>56;
+--------------------+
| 3232261320<<56>>56 |
+--------------------+
|           200 |
+--------------------+
1 row in set (0.00 sec)

With all imperative/procedural languages providing the bit operators (AND, OR, bit shift, etc), one can and should convert the IP string to integer before being loaded to staging. Thus the parsing or conversion can also be easily parallel processed for large loads.

An example in C sharp (C#) is shown below.

// 
// C# program to convert IP address string to an integer.
// 
using System;

namespace IP_addr_to_int
{
    class Program
    {
        static void Main(string[] args)
        {
            // Infinite loop until user enters control-c.
            for (; ; ) {
                string ip = "";
                Console.Write("Enter ip address: ");
                ip = Console.ReadLine();

                ulong ipNum = 0;
                string[] ipArr = ip.Split('.');

                if (ipArr.Length != 4) {
                    Console.WriteLine("Error. Enter four octet IP address only.");
                    // Environment.Exit(-1);
                    continue;
                }
                for (int ii = ipArr.Length - 1; ii >= 0; ii--)
                {
                    ulong ipOctet = Convert.ToUInt64(ipArr[3 - ii]);
                    // ipOctet * 256 * 256 * 256;
                    if (ii == 3)      { ipNum += ipOctet << 24; } 
                    // ipOctet * 256 * 256;
                    else if (ii == 2) { ipNum += ipOctet << 16; } 
                    // ipOctet * 256;
                    else if (ii == 1) { ipNum += ipOctet << 8;  } 
                    // ipOctet * 1
                    else              { ipNum += ipOctet } 
                    // System.Threading.Thread.Sleep(1000); 
                }
                Console.WriteLine 
                    (Convert.ToString(ip)+ " converted to integer " + Convert.ToString(ipNum));
            }
        }
    }
}

For more ways to convert an IP see Part 2 and for tools see Part 3.
Cheers,
Shiva

NFS Cache - Invisible file issue

2010-07-28T15:16:00.000-07:00

I ran into a following situation and it took some time to diagnose the issue and help from couple of folks from DBA and operations team to resolve it. Here is what happened.

I exported a large data set from MySQL to a file in directory /dir_1/dir_2/exported_file.txt, for example, in an application. Then after the file was exported the application went on to consume the file by reading it's content. Since MySQL OUTFILE (exporting data) doesn't overwrite a file if the file name already exists, the code would rename the file to *.bak. See below for pseudo code.

If OUTFILE exists
    Move or Rename OUTFILE to OUTFILE.bak    /* Step 1 */
Run MySQL export to OUTFILE    /* Step 2 */
Check the error code
Read OUTFILE and parse    /* Step 3 */

When I ran the application, it would sometime create the output file and go on to parse it correctly but many a times it would fail in step 1 throwing an error like "file already exists" when in fact it was not. Because I had removed the file with 'rm -f' before rerunning the program. Other times it would fail in step 3 indicating that file does not exists even though SQL exported the file successfully in step 2. I even provided sleep time between each step ranging from 5 to 60 seconds but continued to see the same random behavior.

After spending sometime trying to diagnose what might be going on, ended up debugging NFS caching. The directory /dir_1 was a mounted file system with NFS caching set to few hundred seconds. When the application wrote to NFS directory, the write cache was updated but not the OS directory structure (inode). Reducing the parameter setting (actimeo) to lower number, say 30 seconds, will help alliviate the delay. If sys admins are reluctant to change the older mounted system settings, you should get a new mount point with actimeo set (30).   Once these changes were made application was able to run smoothly with the application sleep set to little higher than actimeo timings. Note, using actimeo sets all of acregmin, acregmax, acdirmin, and acdirmax to the same value. There is no default value. See man pages for more details.

Cheers,
Shiva

Hourly web log analysis through Hadoop

2010-06-16T18:12:00.000-07:00

Many a time one may want to parse the weblogs for doing some quick analysis on AB tests or for security/fraud alerts or recent advertisement or campaign analysis. There are many applications or utilities that perform web log analysis but more often than not regular expressions provide a powerful and elegant ways to analyse these logs and especially handy when one is dealing with massive and quickly rotating web logs. Check out this wiki for more general info on web analytics.

When each weblog is multi-gigabytes and moved to archive every couple of hours and there is farm of web servers (hundreds or thousands of web servers), many of the vendor or 3rd party applications don't scale up either. Hadoop streaming with simple utilities can provide insights into what otherwise would have been a costly experiment.

Here is an regular expession used to extract HOUR and the string that is of interest from the Apache web logs. Each entry in the log has the format similar to the ones below.

01/Jun/2010:07:09:26 -0500] - 127.0.0.1 "GET /apache_pb.gif HTTP/1.1" status=200 size=2326 ab=test1AB ....

01/Jun/2010:07:09:26 -0500] - 127.0.0.1 "GET /apache_pb.gif HTTP/1.1" status=200 size=2326 ab=test-2CD ....

For more details on Apache log configuration, take a look at its specs and custom logs. Along with time of the day of the site hit, user IP address, http request (GET or POST), page requested, protocol used, one can configure the web server to log many more details including referer, user agent (browser), environment variables, etc.

m/(?<=\d{4}:)(\d{2})(?=:\d{2}:\d{2}\s+[-\d{4}]).*?ab=\D+(\d{1}[a-zA-Z]{2,})\b/;

Though the above regular expression may look cryptic, it is straight forward to extract hour and string of interest to us.

(?<=\d{4}:)   => Matches any four digit string (in this case year) FOLLOWED BY ":" and two digits (HOUR) . The beginning "(?<=" is a positive-look-behind assertion engine anchors to look for. If anywhere in the log line two digits appear without preceded by four digits (year) and colon (:), then it is a non-match.

(\d{2})  => Matches two digit string (HOUR). The parenthesis around the two digits activates storing these two digits in a special variable for future use.  As I would like to summarize at the hourly level, I concatenate these digits with other matched string (1AB) and create simple key=value pair.  Value is simply an identity (1) which later then passed to hadoop reducer to count.

(?=:\d{2}:\d{2}\s+[-\d{4}])  => Similar to positive-look-behind, this tells the regular expression engine to look-ahead for positive match from the 2 digit hour string. "(?=" indicates from the current position look-ahead and get colon (:) followed by 2 digits (minutes), another colon (:) 2 digits (seconds) further followed by one or more spaces (\s+).
      [-\d{4}]  => Tries to matches optional ([ ]) "- and 4 digits". This is the token to match time zone (-5000 above).

Once the string string is matched, and hour is extracted by the engine, it then

.*?  => matches any character (denoted by ".") followed by any thing by zero or more (denoted by "*") other characters in non-greedy mode (denoted by "?") until it gets to "ab=" string. Engine actually traces all the way to the end of string and then backtracks it to find "ab=" to meet the minimal match of ".*?".

ab= => environment variable that is of interest to us followed by

\D+  => any non-digit character followed by one or more times until it trys to match

(\d{1}[a-zA-Z]{2,}) => a string to match that has single digit followed by two or more characters (a to z, lower or upper case) and store this match for future use until

\b => a word boundary. A boundary between a word character that includes "a to z, 1 to 9 and _". You can visualize this as a thin line-boundary between word characters and any non-word characters like "." or "-" or "$", etc.

In the parser application, you can concatenate first match (Hour - 07) and second matched string (Test string - 1AB) like "1AB_07" and set the value to "1".

When the Hadoop mapper passes this to reducer, reducer simply has to count the 1s and to get sum of hourly hits of "1AB". You can then load this data to some table at (date and) hourly level and maintain its history for further analysis. Similar analysis on web logs can yield IP level, page level and many other useful info.

Note: While using regular expression it helps to understand how the backtracking technique works and its implications on performance. Backtracking happens when one uses quantifiers like "*, *?, +, +?, {n,m}, and {n,m}?".   Take a look at interesting articles here and here.

Conversation Prism - An Image

2010-06-04T16:36:00.000-07:00

As the social media, social networking, advertising, Internet marketing continue to evolve with new technologies and many companies create their own social groups, it all gets more complex and confusing. Many a times a picture or an image will explain more elegantly than 1000 words are more and in some cases image is the most suited tool to explain. Here is one image created by Brian Solis & Jesse Thomas that I like in this conversation!

Few other images that will help.

From Dave Fleet
(http://www.socialmediatoday.com/SMC/162110)

From FredCavazza.net

Enjoy,
Shiva

Email parsing with regular expressions

2010-05-29T15:17:00.000-07:00

Emails always pose interesting challenges to load and handle for doing any kind of analysis with them - analysing number of users from email providers like Yahoo, Google, Hotmail, etc. or finding top country specific domains or users with specific email pattern.

RFC 822, 2822 (April, 2001) and 5322 (October,2008) define the specification and syntax for text messages exchanged between computers. The RFCs cover not just the email address specifications but also the envelop and contents. Most of applications adhere to subset of these specs with more stringent email addresses. For example, though address specs allow for ALPHA (a-z, A-Z), DIGITS (0-9) and special characters like "!", "#", "$", "%", etc. and even more special characters with quoted strings. But typically email service providers won't allow you to create an email address with special characters like "$" or "!", etc. For more spec details see section 3.4 and 3.4.1 in rfc5322. RFC 3696 explains in much easier terms email addresses, URI and HTTP URL.

One can load email addresses in to staging area of data warehouse (DW) and then perform validation and cleaning through ETL's in-built utilities or components. But to handle many of different possibilities and ability to quickly update or modify any new format encountered, it is better to validate and clean it through application programs before loading into staging or final tables. In this way the load can also be distributed through file split and multi-threading or processes running on different systems.

Following regular expression pulled from regular-expressions.info handles all most all email addresses (more than 99.9%) and you can tweak to it suit your needs for performance or handling specific emails only. For example, you may not want to allow any email that has special characters like #, %, $, {, }, /, *, etc. I have explained below in detail how this RE parses an email.

[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+(?:[A-Z]{2}|com|org|net|edu|gov|mil|biz|info|mobi|name|aero|asia|jobs|museum)\b

Same as above with color coding for further explanation:
[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+(?:[A-Z]{2}|com|org|net|edu|gov|mil|biz|info|mobi|name|aero|asia|jobs|museum)\b

Address mainly has two parts - one before (part 1) "@" and one after it (part 2).

Part 1:

[a-z0-9!#$%&'*+/=?^_`{|}~-] -- A set of characters i.e., between square brackets [ ], and in this case all letters "a to z" (either upper case or lower case) and numerals "0 to 9" and special characters "!#$%&'*+/=?^_`{|}~-" are allowed
+ -- One or more times of the above character set. Meaning "joe" or "!j!" or "abc" are all valid i.e., Alphabets and ! are repeated one or more times. But Part1.1 will not match "(abc)" or "joe<>" because "(", ")", "<", ">" are not in the above character set.
( -- Allow grouping also start remembering the matched string. To avoid storing use "?:" right after "(".
?: -- Don't need to remember or store what is matched. When grouping is done through "(", it is also remembered in variables $1, $2, $3, etc. With "?:" following "(" indicates that not to store it. This will help with the performance but if you want the matched string to use for further processing, then you can remove "?:" from above regular expression.
\. -- It indicates "." can appear in email after bullet 1 above but "." has significance for the regular expression (RE) itself. In RE "." means any single character so we need to escape it and pass it "." down to parsing engine.
[a-z0-9!#$%&'*+/=?^_`{|}~-] -- See 1 above.
+ -- See 2 above. Also, if a "." appears then there must be at least one of characters in 6.
* -- Means zero or more times. In this case all characters in step 5, 6 & 7 can be optional. That is "joe.a" is valid but not "joe."
@ -- "@" sign is a must in the email address. It should appear once.

Part 2:

Part 2A: (?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+

+ -- All with the outer parenthesis must appear at least once. It enforcing rule that after "@" sign in email, there must be at least one charcter.
?: -- Don't need to remember or store what is matched. Helps in performance when dealing with millions of email parsing.
[a-z0-9] -- First character after the "@" must be a alpha-numeral (a,b,c...z or 1,2,...9)
(?:[a-z0-9-]*[a-z0-9])? -- "?" at the end indicates 0 or 1 time of alpha-numeral characters. That is, after a first character in step 3 next characters must be alpha-numerals only but they are optional.
\. -- See step 5 in part 1.

Part 2B: (?:[A-Z]{2}|com|org|net|edu|gov|mil|biz|info|mobi|name|aero|asia|jobs|museum)

[A-Z]{2} -- Any two characters after the final ".". This covers country top level domains (TLD) like "uk" (for United Kingdom), "in" (India), "ca"(Canada), etc. See this list of standard country codes.
| -- Is a "OR" operator. It matches "com" or "org" or "museum", etc.

And final "\b" at the end is a word boundary anchor.

During this validation and clean up you can also add length check on the email part 1 and 2. RFCs specify of length 64 characters (octet) for part 1 (before @ character) and 255 characters (octet) after @ symbol. These implementations can effectively handle more than 99.999% of emails.

Let me know if you have different regular expression or different way of parsing emails.

HTH,
Shiva

Data Profiling - An example in Talend Profiler

2010-05-20T17:45:00.000-07:00

Data is loaded into Data Warehouse (DW) from disparate systems and sometimes from external partners who have their own coding standards. In any case, the quality of the data loaded into the data warehouse is often variable,and especially while discovering the data one may need to load some sample data and do some analysis including initial data profiling. During this process one may discover differences which when resolved result in much smoother data flow along the process flow. Or it may be at later stage, say after the summarization is completed one may need to do some analysis on type of data. In all these cases data profiling helps and Talend provides a tool (Talend Open Profiler - TOP) to quickly and efficiently perform profiling.

Data profiling - the process of examining available data in different data sources, including databases, applications, files, data transfer from external systems etc., and collecting statistics and information - improves data quality and better reporting.

In date dimension, we have nearly 220,000 rows covering Jan.01,1900 to Dec.31,2500 (7 hundred year dates) and one of the column is 'day_of_week_name' (cardinality 7 - MONDAY, TUESDAY....). This table has 70 columns including date, weeks, months, names, etc. For testing purpose, I wanted to check the nulls and pattern frequency (distribution) for 'day_of_week_name' column.

To do so, select the column to profile (day_of_week_name), drag and drop into "Analyzed columns" of "Analysis Settings" tab. Then pick the indicators i.e., how you want the column measured (count, range, stats, etc.) and I picked row count and NULL count along with "Pattern Frequency Table". Pattern frequency will count different patterns. The results in "Analysis Results" tab shows as below.

There were 219,146 row count with no NULLs and the pattern frequency indicates 31,307 of pattern AAAAAAAAA (9A Uppercase letters), 31,307 of 7A pattern, 62,613 of 8A pattern and 93,919 of 5A pattern.

9A pattern count is of 'WEDNESDAY' rows, 5A pattern covers 'SUNDAY', 'MONDAY' and 'FRIDAY'. Similarly for other days.

You can also have your own 'UDI' - User Defined Indicators that can add more functionality to existing indicators. You can build them in Java jar and import them. But anytime you do processing in the profiler, the data needs to get transferred from database and possibly slowing down the profiling. For smaller data set it may not be noticeable but any profiling on large tables can bog down either due to memory limitation and/or network delay.

Profiler converts your indicators to appropriate query and runs it. For example, for the above pattern match, MySQL query looks like:

SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(`day_of_week_name`,'a','a'),'b','a'),'c','a'),'d','a'),'e','a'),'f','a'),'g','a'),'h','a'),'i','a'),'j','a'),'k','a'),'l','a'),'m','a'),'n','a'),'o','a'),'p','a'),'q','a'),'r','a'),'s','a'),'t','a'),'u','a'),'v','a'),'w','a'),'x','a'),'y','a'),'z','a'),'ç','a'),'â','a'),'ê','a'),'î','a'),'ô','a'),'û','a'),'é','a'),'è','a'),'ù','a'),'ï','a'),'ö','a'),'ü','a'),'A','A'),'B','A'),'C','A'),'D','A'),'E','A'),'F','A'),'G','A'),'H','A'),'I','A'),'J','A'),'K','A'),'L','A'),'M','A'),'N','A'),'O','A'),'P','A'),'Q','A'),'R','A'),'S','A'),'T','A'),'U','A'),'V','A'),'W','A'),'X','A'),'Y','A'),'Z','A'),'Ç','A'),'Â','A'),'Ê','A'),'Î','A'),'Ô','A'),'Û','A'),'É','A'),'È','A'),'Ù','A'),'Ï','A'),'Ö','A'),'Ü','A'),'0','9'),'1','9'),'2','9'),'3','9'),'4','9'),'5','9'),'6','9'),'7','9'),'8','9'),'9','9'), COUNT(*) c 
FROM `dw_mktg`.`dim_date` t  
GROUP BY REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(`day_of_week_name`,'a','a'),'b','a'),'c','a'),'d','a'),'e','a'),'f','a'),'g','a'),'h','a'),'i','a'),'j','a'),'k','a'),'l','a'),'m','a'),'n','a'),'o','a'),'p','a'),'q','a'),'r','a'),'s','a'),'t','a'),'u','a'),'v','a'),'w','a'),'x','a'),'y','a'),'z','a'),'ç','a'),'â','a'),'ê','a'),'î','a'),'ô','a'),'û','a'),'é','a'),'è','a'),'ù','a'),'ï','a'),'ö','a'),'ü','a'),'A','A'),'B','A'),'C','A'),'D','A'),'E','A'),'F','A'),'G','A'),'H','A'),'I','A'),'J','A'),'K','A'),'L','A'),'M','A'),'N','A'),'O','A'),'P','A'),'Q','A'),'R','A'),'S','A'),'T','A'),'U','A'),'V','A'),'W','A'),'X','A'),'Y','A'),'Z','A'),'Ç','A'),'Â','A'),'Ê','A'),'Î','A'),'Ô','A'),'Û','A'),'É','A'),'È','A'),'Ù','A'),'Ï','A'),'Ö','A'),'Ü','A'),'0','9'),'1','9'),'2','9'),'3','9'),'4','9'),'5','9'),'6','9'),'7','9'),'8','9'),'9','9') 
ORDER BY c DESC LIMIT 10

In other DBMS like Oracle, PostgresSQL and DB2 it will be much smaller query since those systems provide TRANSLATE function.
Profiler provides many built-in indicators like Soundex Frequency, Mean, Median, Mode, Range, Quartile Range, etc.

HTH,
Shiva

Logging queries for performance monitoring

2010-05-07T12:33:00.000-07:00

Before making any performance improvements, one need to measure it either you are trying to improve computer applications or DW queries, etc. In one of the Data Warehouse subject area where I run more than 120K (120,00) queries everyday in an ETL environment, I log all queries to a table and track over time to see which queries are showing performance issue(s). With the following table, I was able to improve the performance by more than 50% some time. For a query that repeatedly runs with different parameters in where clause, a small improvement adds up quickly and other times due to data skewing you can clearly see any changes needed to improve performance. Other times any mistakes in indexes (DBA dropped it :) or application user modified their query, etc. you will have a reference point to check against why a particular query or queries are slow.

Query Performance Monitoring table definition
Column	Null?	Type	Comment
ID	No	int (11)	Auto Increment
RUN_NUMBER	No	tinyint	Useful when job is recovered or re-ran
APPLICATION	No	varchar(128)	Application name to track
DOMAIN	Yes	varchar(128)	Application domain or subject area
QUERY	No	varchar(4096)	Actual query
DURATION	No	decimal(10,5)	How long did query run?
ROW_COUNT	Yes	int	Number of rows affected
RESULT	No	varchar(32)	Query result type - succeeded or failed?
COMMENT	Yes	varchar(128)	User comment that can be logged with each query
CREATED_DT	No	date	Query run date
CREATED_DT_TM	No	timestamp/ datetime	Query run date and time
CREATED_BY	Yes	varchar(64)	Query user name

In the above table, one can use CREATED_DT_TM wherever CREATED_DT is needed while doing query performance analysis but most of these analyzes are done at date level (as most of DW ETL and summarizations are daily processes). By having date column and indexed, the query analysis queries are much faster as there is no need to apply date function on each row.

More interesting analysis including job failures, errors and query result set (ROW_COUNT) can be done with this data. You can also analyze specific database query performances. For example when a same query runs against different (mysql) databases (say, more than 100), some databases show performance getting worse due to table size or load, etc.

Once you know that there is some query performance issue, you can take remedial action. Let me know how you have implemented query performance monitoring?

Cheers,
Shiva

Google Analytics Plugin - Very useful one

2010-05-06T15:05:00.000-07:00

Google Analytics (GA) is invaluable when you need to slice and dice web traffic data along numerous dimensions. For example, by date, time, location just to name few measuring visits, vistiors, pageviews, etc. For full list of possible combinations take a look at this documentation.

While many business users and SEO/SEM analyts use GA on regular basis to manage their Internet Marketing spend and improve site traffic for better ROI, they can chart a single measure for different segments and analyse one metric at a time. Many a time users download their data from adwords or get GA data through their in-house API setup. With almost all business users having used Excel as one of their main analytical tool, they prefer it over other tools (either in-house or 3rd party). Also, as quickly as things change in Internet Marketing (IM) world, some quick analysis business user can do on their own allows them to respond to quickly to market changes. You may find bullet 3 and 4 in this article from Avinash Kaushik relevant.

One freeware plug-in that provides the solution is ExcellentAnalytics' plug-in for Excel.

ExcellentAnalytics' Plug-in

It is pretty simple to use and an user can start using it immediately. Once the data that you are interested in is downloaded through this plug-in, you can exploit Excel abilities to further analyze the data without any other (IT) application needed.

After downloading and installing the plug-in, select "ExcellentAnalytics" menu at the top when you bring up MS Excel 2007 and click on "Account" button; then enter your GA email and password for it to connect to GA. Click on "New Query" and select the profile of interest to you and select valid dimensions and metrics. If it is an invalid combination or query, the result set does not return any and hopefully future release of this plug-in will provide some user friendly info.

There were queries that didn't return right result sets. For example, "Visitor Recency" metrics like "most visits the previous visits happened: X days ago". Or under Filter section dimensions are assumed to be string type and any dimension that is a number can't be entered. For example, you selected "visit count" dimension and want to filter any visit count less than 5. You can't. Hey, it is a freeware and it does a decent job for quick analysis. Use it with caution and having said that you can always check the numbers against GA's report to make sure the plug-in didn't mess up anything.

Sample reports:

HTH,
Shiva

Special characters in table names

2010-04-29T15:11:00.000-07:00

In DW environment, we allow business user to upload data into new tables and run their own adhoc queries when needed. The user interface allows the user to upload up to 10,000 (10K) rows in either comma or tab delimited format to a temporary database/table. The user can specify the table name they want the data to upload to. Many savvy business users can thus run their own SQL queries joining with the data in DW tables.

All this works fine but many a times user provides table names with non-alphanumeric characters including space, forward or backward slash, dollar sign, etc. At some point DBAs noted that managing these tables becoming an issue especially the tables names with special characters. The following monitoring tool helped alleviate or eliminated the problem and also the UI was modified to check for any special characters and remove them before creating users' temporary tables.

Since MySQL doesn't provide regular expression (RLIKE or REGEXP) in SHOW TABLE syntax one is forced to use UNIX or other language (awk, perl, python, etc.) for RE features.

The following pulls all table names with special characters including $, \, space, ?, and @. You can add other characters that are of interest to you to this set.

linux> mysql --host=hostname_or_ip  --database=db_name   --user=root  --password=your_pw  --execute="SHOW TABLES" | egrep [$\\\ ?!@]

# This shows tables like (not including double quotes)
"non_alphanum_table_name test it 2"
"non_alphanum_table_name; test it"
"non_alphanum_table_name; test it $"
"non_alphanum_table_name; test it $ \"
"non_alphanum_table_name_test_$_!_2"

Here you have choice either delete those tables or store the name of these tables into a separate table called "ERROR_TABLE_NAME" and work with business users to later modify them. Option 2 is friendly and professional and you won't annoy users like in option 1 :).

Quickly zap an awk script like

{ print "INSERT INTO ERROR_TABLE_NAME(name) VALUES('" $0 "'); COMMIT;" }

to insert each table name into ERROR_TABLE_NAME table.

You need to store the script in a file (say, error_tbl_name.awk) since at command line passing a single quote in the above string doesn't work. When you pass a table name to MySQL, you need to quote the name like 'non_alphanum_table_name test it 2'. But single quote is used by awk which was not easily escaped and errored out.

Now piping all three commands above will result in

linux> mysql --host=hostname_or_ip  --database=db_name   --user=root  --password=your_pw  --execute="SHOW TABLES" | egrep [$\\\ ?!@] | awk -f error_tbl_name.awk

-- The INSERT statements generated for each looks like
INSERT INTO ERROR_TABLE_NAME(name) VALUES('non_alphanum_table_name test it 2'); commit;
INSERT INTO ERROR_TABLE_NAME(name) VALUES('non_alphanum_table_name; test it'); commit;
INSERT INTO ERROR_TABLE_NAME(name) VALUES('non_alphanum_table_name; test it $'); commit;
INSERT INTO ERROR_TABLE_NAME(name) VALUES('non_alphanum_table_name; test it $ \\'); commit;
INSERT INTO ERROR_TABLE_NAME(name) VALUES('non_alphanum_table_name_test_$_!_2'); commit;

Also, note you have commit after each insert, due to fact that the auto commit is turned off and when piping is done mysql command is executed for each table which will generate error otherwise.

As final step execute

linux> mysql --host=hostname_or_ip  --database=db_name   --user=root  --password=your_pw  --execute="SHOW TABLES" | egrep [$\\\ ?!@] | awk -f error_tbl_name.awk |  mysql --host=hostname_or_ip  --database=db_name   --user=root  --password=your_pw

Change these table names to new standard table names and send an email to business users with list of above table names.

There you have it.
HTH,
Shiva

Getting MySQL DB size

2010-04-24T17:07:00.000-07:00

Many a times you want to find the size of database (schema) for performance monitoring or to allocate more disk space or to take some proactive action on db size. You can pull relevant information from MySQL INFORMATION_SCHEMA with the following query. It sums up each table data and its index size for all tables in a single schema.

SELECT s.schema_name,
  CONCAT(IFNULL(ROUND((SUM(t.data_length) +
       SUM(t.index_length))/1024/1024,2),0.00),"Mb") total_size,
  CONCAT(IFNULL(ROUND((SUM(t.data_length))/1024/1024,2),0.00),"Mb") data_size,
  CONCAT(IFNULL(ROUND((SUM(t.index_length))/1024/1024,2),0.00),"Mb") index_size,
  CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length)) -
         SUM(t.data_free))/1024/1024,2),0.00),"Mb") data_used,
  CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00),"Mb") data_free,
  IFNULL(ROUND((((SUM(t.data_length)+SUM(t.index_length)) -
       SUM(t.data_free))/((SUM(t.data_length) + 
       SUM(t.index_length)))*100),2),0) pct_used,
  SUM(t.table_rows) total_rows,
  COUNT(t.table_name) total_tables,
  CONCAT(ROUND(MAX(t.data_length+t.index_length)/1024/1024, 0.00), 
      "Mb") biggest_table_size,
  GROUP_CONCAT(distinct t.engine) engines_used,
  GROUP_CONCAT(distinct t.table_collation) collations_used
FROM INFORMATION_SCHEMA.SCHEMATA s
  LEFT JOIN INFORMATION_SCHEMA.TABLES t 
ON s.schema_name = t.table_schema
WHERE s.schema_name = "test_dw_advertising"
GROUP BY s.schema_name\G


*************************** 1. row ***************************
       schema_name: test_dw_advertising
        total_size: 212151.14Mb
         data_size: 154660.83Mb
        index_size: 57490.31Mb
         data_used: 210039.45Mb
         data_free: 2111.69Mb
          pct_used: 99.00
        total_rows: 309940227
      total_tables: 118
biggest_table_size: 74360Mb
      engines_used: InnoDB,MyISAM
   collations_used: latin1_swedish_ci
1 row in set (1 min 38.26 sec)

The total size of allocated (including free data size) is nearly 212G, out of which data used ~155G and indexes used ~57G for 118 tables leaving 2G free (99% of disk allocated is used). Biggest table used 74.3G disk space and this schema has both InnoDB and MyISAM tables using single collation latin1_swedish_ci.

HTH,
Shiva

Talend - Oracle - Java Path error

2010-04-08T18:14:00.000-07:00

Few weeks ago on of our QA systems I installed Oracle 10g Express for testing and installation was smooth and all went well. But recently, I needed to do some data profiling in a specific table in MySQL DW environment due to fact that some of queries were taking more than twice the amount of normal runs. Specifically, one query that used to take 5-6 minutes churning through couple of million rows in a staging table started taking more than 15 minutes. I suspected some data issue but needed to quickly run some tests knowing how the recent data profiles against earlier weeks data.

I downloaded Talend Data Profiler to this new test machine (where I had Oracle 10g) and tried to install it. Soon after double clicking on exe file, I got an error indicating something like "org.talend.rcp.branding.top.product could not be found." in configuration\12725000123.log. The log also has some more information regarding which Java version it is trying to use and command line options passed to run it. The error itself doesn't indicate much and this wasted quite a bit of time.

Going through manual of Talend I found out that profiler needs Java version 1.5 or later version. But I was pretty sure that I had the latest Java environment on this box and checked twice by running java -version in cygwin and checking the path. Also by visiting http://www.java.com then clicking on "Verify Installation" button. They both indicated latest version.

cygwin$ java -version
java version "1.6.0_20"
Java(TM) SE Runtime Environment (build 1.6.0_20-b02)
Java HotSpot(TM) Client VM (build 16.3-b01, mixed mode, sharing)

cygwin$ which java
/cygdrive/c/WINDOWS/system32/java

Then on checking Windows "path" through
Start->Settings->Control Panel->System->Advanced Tab->Environment Variables->path,
found out that Oracle had stuck its own java directory (version 1.4.2) in the beginning which was throwing off Talend installation. I simply moved the oracle's path variable values
(C:\oraclexe\app\oracle\product\10.2.0\server\bin;C:\OraHome_1\bin;C:\OraHome_1\jre\1.4.2\bin\client;C:\OraHome_1\jre\1.4.2\bin;)
to the end of path string and restarted the installation. This time data profiler found the right Java and all went well.

I wish Talend had thrown a better exception indicating which version of Java it needs and which version it found.
HTH,
Shiva

Yahoo SEM isBooksClosed

2010-03-29T15:37:00.000-07:00

As a part of Internet Marketing and SEM, you will need to pull daily summary of keyword performance reports from search engines like Google, Yahoo and MSN.

In case of Yahoo, you should check "isBooksClosed" to get the correct data for your report dates; meaning the data you pulled will not change if isBooksClosed is true and Yahoo analytics has collected all the data for the date you requested the report.

This is especially important if you are pulling the previous day data early in the morning as Yahoo may not have had time to get collect and complete the analytics. We have run into many times books not closed condition for few accounts out of many tens of accounts. We then need to repull the data at later time of the day and run the daily summaries.

I have reproduced the part of Yahoo explaination related to this from http://developer.searchmarketing.yahoo.com/docs/V6/gsg/BasicReportService.php#books_closed

Books Closed

It is recommended that you wait until the books are closed before running a report. Use the isBooksClosed operation to periodically check the status of books. After the report is run, you may also check the information in the report header. See Report Headers.

isBooksClosed = (Books Closed Time > Inclusive End Date)

If the books closed time is greater than the inclusive end date, the full 24 hours of the report end date, then isBooksClosed is true and the data in the report is considered complete. If the books closed time is less than the inclusive end date, then isBooksClosed is false and the data in the report is considered incomplete.

Say your report dates are 2009.11.01 to 2009.11.03 (November 1, 2009 to November 3, 2009) and you made a API request on Nov.4th morning 3am for an account's "KeywordSummaryByDay" and there is a possibility that Yahoo has not closed the book for Nov.3rd data. By making a SOAP call to BasicReportService with report name "isBooksClosedStatus" you be sure whether data is finalized on Yahoo side for Nov.3rd. I have seen accounts or campaigns with tens of thousands of keywords not closed by 3am the next day. So make sure that you are pulling the right data for your DW.

So, always make sure that you check for isBooksClosed.
HTH,
Shiva

Google's extended searches

2010-03-26T14:18:00.000-07:00

Internet search users are very well aware of Google search engine and typically they end up entering their query or keywords in the search box and click the resulting links of interest/relevant to them (typically first page results and especially among top 3 o 4 results). Large percentage of users seem to be unaware of Google's extended search that can provide exact result he or she is looking for without having to hop to one more site.

For example, weather for a particular city or current time at a location or dictionary or area code or sports scores and many more. Following are few examples.

For weather: To find current weather at Boston, USA just enter keywords "weather Boston, USA" and you will see today and next few days weather in the first result!

For time: To find current time at Bangalore, India (or Bengaluru, India) enter "time Bengaluru, India" and first result is the time.

For dictionary: use "define" keyword first before entering your query like "define avatar"

For stocks: This one is little too specific for many users because one need to know specific stock's ticker symbol and there more than 10,000 of the them in US. It doesn't seem to work for international stocks.

Area code: Enter US 3 digit area code to get phone's area code info.

Fill in the Blank: My favourite when searching something for kids home work or a pop quiz. Try "Einstein got Nobel prize in *" or "Earth circumference is * miles"

There are many more that can save time and typing or mouse clicks! Check out Google's search tips.

Better yet for all these queries just use "WolframAlpha knowledge engine".

Cheers,
Shiva

Yahoo SEM daily report - Currency and Dups handling

2010-03-16T13:43:00.001-07:00

If you are pulling daily keyword performance data from major search engines to store in your in Data Warehouse, you need to perform many of transformation before having a daily, weekly or monthly summaries available for business (end) users. In the case of Yahoo the required transformations that I needed to make are, one to handle the currency conversion and two to handle Yahoo sending more than one keyword when only one is expected. This has seem to happen when query or landing page url is modified for the same bid unit and for high impressions queries. Also, for disabled queries/keywords.

The following sql handles both with data read from staging table and loaded into final table after some transformation and cleaning. It does currency conversion for all countries except US and Mexico (units are in US dollars) which do not need conversions.

Sub-query below uses MySQL GROUP_CONCAT to find any duplicate keywords for the bid unit and dim_curreny has the daily currency exchange rates pulled from external third party services early in the morning. se_query_group table has the grouping of the keywords based on company requirements and search engine. Query also filters out any keyword that has zero impressions.

The query can further be modified for better performance of using query_id by joining with data warehouse query tables and using query_id in the joins also using search engine bid unit as the surrogate key.

INSERT INTO INT_MKTG_KEYWORD_PERF
    (se_date, search_engine, account_id, campaign_id, ad_group_id, keyword_id
    ,domain, url, query_group_id
    ,query, query_num_2
    ,impressions, clicks, ctr
    ,cpc_orig, cpc
    ,revenue_orig, revenue
    ,cost_orig, cost
    ,num_assist
    ,avg_position )
SELECT KP.se_date, KP.search_engine, KP.account_id, KP.campaign_id
       ,KP.ad_group_id, KP.keyword_id, 
       ,KP.domain, KP.url, KP.query_group_id
       ,CASE
           WHEN query2 NOT RLIKE  '---'  THEN  query2                        # Normal single kw
           WHEN query2 RLIKE  '---'  THEN  substring_index(query2, '---', 1) # Get 1st kw
       END query2
       ,CASE
           WHEN query2 RLIKE  '---'  THEN  substring_index(substring_index(query2, '---', 2), '---', -1)
       END  query_num_2                                            # Get 2nd query
       ,impressions
       ,clicks
       ,ctr
       ,cpc cpc_orig
       ,CASE
           WHEN  C.country_code not in ('US', 'MX') THEN cpc / C.currency_rate
           ELSE cpc
       END cpc
       ,revenue revenue_orig
       ,CASE
           WHEN C.country_code not in ('US', 'MX') THEN revenue / C.currency_rate
           ELSE revenue
       END revenue
       ,cost cost_orig
       ,CASE
           WHEN C.country_code  not in ('US', 'MX') THEN cost / C.currency_rate
           ELSE cost
       END cost
       ,CASE
           WHEN avg_position > 0 and avg_position < 1 THEN 1
           ELSE avg_position
       END avg_position
     FROM (
          SELECT se_date, search_engine
              ,account_id, campaign_id, ad_group_id, keyword_id, 
              ,domain, url, query_group_id, query
              ,GROUP_CONCAT(query SEPARATOR '---') query2
              ,SUM(impressions) impressions, SUM(clicks) clicks
              ,AVG(ctr) ctr, AVG(cpc) cpc, 
              ,AVG(revenue) revenue, 
              ,SUM(cost) cost, AVG(avg_position) avg_position
          FROM stg_keyword_perf
          WHERE se_date >=  '<date>' #-- Date here
          GROUP BY se_date ,account_id ,campaign_id ,ad_group_id ,keyword_id
          ) KP #- stg_kw_perf
     JOIN se_query_group QG
       ON KP.query_group_id = QG.query_group_id AND KP.search_engine = QG.search_engine
     JOIN dim_currency C 
       ON KP.se_date = C.currency_date AND QG.country_code = C.country_code
     WHERE KP.se_date = '<date>'   #-- Date here
       AND KP.impressions <> 0
      ;

HTH,
Shiva

Ruby libmysql.dll or mysql_api.so error

2010-03-15T17:41:00.000-07:00

Installing Ruby MySQL module is pretty straight forward either on *nix or Windows by running

> gem  install  mysql

But on Windows when you try to import the module through "require mysql" you may get an error like

C:/Ruby/lib/ruby/gems/1.8/gems/mysql-2.8.1-x86-mswin32/lib/1.8/mysql_api.so: 126: The specified module could not be found.   - C:/Ruby/lib/ruby/gems/1.8/gems/mysql-2.8.1-x86-mswin32/lib/1.8/mysql_api.so (LoadError)
#  from C:/Ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:in `require'
...
...

In that scenario, you are missing "libmysql.dll" in the system32 directory. In my case I had MySQL server copy and had the dll in folder C:/Program Files/MySQL/MySQL Server 5.4/lib/opt

From cygwin window do

> cp  /cygdrive/c/Program\ Files/MySQL/MySQL\ Server\ 5.4/lib/opt/libmysql.dll   /cygdrive/c/WINDOWS/system32/libmysql.dll

Now run your Ruby db test program

require "mysql"

begin
  dbh = Mysql.real_connect("db_server_host", "user_name", "password", "database_name")
  puts "Server version:" + dbh.get_server_info
rescue Mysql::Error => err
  puts "Error code: #{err.errno}"
  puts "Error: #{err.error}"
ensure
   dbh.close if dbh
end

HTH,
Shiva

Mapping URL to top level domain

2010-03-12T17:24:00.000-08:00

When parsing web logs as part of web analytics, one might be interested in the top level domain analysis before drilling down further. Following query maps an url (referer) to top level domain. Top level domain include not only generic ones like '.com', '.edu', '.org', etc but also country level top levels like '.uk', '.de', etc. You can download the list from IANA (Internet Assigned Numbers Authority) .

After downloading it import into a table dim_int_mktg_tld which you can join with the staging data.

CREATE TABLE `dim_int_mktg_tld` (
  `tld` varchar(16) NOT NULL,
  `type` varchar(32) default 'country-code',  #-- Can be country-code or generic
  `info` varchar(500) default NULL,
  PRIMARY KEY  (`tld`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

#--  Join with yesterdays staging data and get TLD
SELECT WL.url, TLD.tld
FROM stg_weblog_date WL
LEFT OUTER JOIN dim_int_mktg_tld TLD
ON substring_index(
     substring_index(
       substring_index(url, '://', -1),
       '/',1), 
    '.',-1) = TLD.tld
WHERE WL.date = DATE_ADD(CURRENT_DATE, interval -1 day)

#-- Example: http://www.abc.co.au/in/test
#-- Innermost substring_index returns everything to the right of '://' 
#--   i.e, www.abc.co.au/in/test,
#-- then the middle one gets everything to the left of first '/'
#--   i.e, www.abc.co.au
#-- then the outer most substring_index returns everything to the right of last '.'
#--   i.e, au
#-- which is then joined with TLD table

HTH,
Shiva