Shard-Query blog

The only open source MPP database engine for MySQL

Tag Archives: sq_helper

Access Shard-Query with the MySQL client without using MySQL proxy

One of the great features of Shard-Query is the ability to use MySQL proxy to access resultsets transparently. While this is a great tool, many people have expressed reservations about using MySQL Proxy, an alpha component in their production environment.

I recognize that this is a valid concern, and have implemented an alternate method of retrieving resultsets directly in the MySQL client, without using a proxy. This means that any node can easily act as the “head” node without any extra daemon, instead of having to run many proxies.

The sq_helper() routine has been checked into the git repository and is available now.

The function takes a few parameters:

  • sql to run
  • shard-query schema name (empty string or null for default schema)
  • schema to store temp table in
  • temp table name (where results are sent to)
  • return result (boolean, 1 returns result to client, 0 does not return the result)
  • drop table (boolean, 1 drops the table at the end of the procedure, 0 does not)
mysql> call shard_query.sq_helper("select * from dim_date limit 2", "", 'test','testtab',1,1)\G
*************************** 1. row ***************************
         D_DateKey: 19911231
            D_Date: December 31, 1991
       D_DayOfWeek: Wednesday
           D_Month: December
            D_Year: 1991
    D_YearMonthNum: 199112
       D_YearMonth: Dec1991
    D_DayNumInWeek: 4
   D_DayNumInMonth: 31
    D_DayNumInYear: 365
  D_MonthNumInYear: 12
   D_WeekNumInYear: 53
   D_SellingSeason: Christmas
 D_LastDayInWeekFl: 0
D_LastDayInMonthFl: 0
       D_HolidayFl: 0
       D_WeekDayFl: 1
*************************** 2. row ***************************
         D_DateKey: 19920101
            D_Date: January 1, 1992
       D_DayOfWeek: Thursday
           D_Month: January
            D_Year: 1992
    D_YearMonthNum: 199201
       D_YearMonth: Jan1992
    D_DayNumInWeek: 5
   D_DayNumInMonth: 1
    D_DayNumInYear: 1
  D_MonthNumInYear: 1
   D_WeekNumInYear: 1
   D_SellingSeason: Winter
 D_LastDayInWeekFl: 0
D_LastDayInMonthFl: 1
       D_HolidayFl: 1
       D_WeekDayFl: 1
2 rows in set (0.07 sec)

Important:
The only requirement is the Gearman UDF (which is also required by the proxy). Don’t forget to call gman_server_set(…) with your gearman server, or this function won’t work.