Query Results by pages – Query Caching Method


Pagination of query results for web application to show data by pages is a very common requirement across all types of application. Here is a solution to overcome certain common issues that we have today . The very popular solution in MySQL for this now a days is to use OFFSET and LIMIT with the select queries. The disadvantage  with using OFFSET and LIMIT is each time when user request for a page, database engine process the entire data, sort it and calculate the particular page user asks for and return. In order to avoid this repeated full data process, we are doing a full data process first time , cache it and reuse the data. We also manage the cache to expire it appropriately.

We are going to achieve this using a search stored procedure and cache index table. The cache index table keeps the active sessions of cache.

create table paginationHdr (sessionid varchar(32),queryid int,temptable varchar(100),whereclause varchar(1000))

We keep the sessionid here to identify users session. Queries can be written inside using switch case , so that the procedure can be used for multiple queries. The best way is to keep this in a separate table so that we do not need to modify the procedure each time we insert a query. temptable field is nothing but the cache table name for the particular call.

Another thing is to keep in mind is that, when a web session expires, make sure you do the following things.

  1. drop all cache tables created for the expiring session ( Select temptable from paginationHdr where sessionid ='<session>’;)
  2. delete all records from the cache index table (paginationHdr) with the expiring sessionid

The procedure must be called as –

CALL spSearch(11,’12345678901234567890123456789012′,0,1,’ AND 1=1 ‘,’nameColumn’);

This stored procedure is implemented in MySQL but you can translate it pretty much to any relational database like SQL Server etc.

Pagination

delimiter //
create procedure spSearch(p_queryid int, p_sessionid varchar (32),p_refresh bit,p_page int,p_whereclause varchar(1000),p_orderby varchar(100))
BEGIN
Set @v_pagesize = 3;
Set @v_basequery =null;
Set @s=null;

CASE p_queryid
WHEN 1 THEN
SET @v_basequery = ‘ SELECT idColumn,nameColumn, @curRank := @curRank + 1 AS rnk99, 0 AS pg99 FROM table1,(SELECT @curRank := 0) r WHERE active=1 ‘;

WHEN 2 THEN
SET @v_basequery = ‘ SELECT idColumn,nameColumn, @curRank := @curRank + 1 AS rnk99, 0 AS pg99 FROM table2,(SELECT @curRank := 0) r WHERE active=1 ‘;

END CASE;

Set @v_temptable=null;
SELECT temptable INTO @v_temptable FROM paginationHdr WHERE sessionid=p_sessionid AND 0=p_refresh AND queryid=p_queryid AND whereclause = p_whereclause;

IF @v_temptable is null THEN — This is for first time data pulling
Select CONCAT(‘TEMP’,SUBSTRING(MD5(RAND()) FROM 1 FOR 10),left(p_sessionid,5) ) into @v_temptable ;

LoopStart: LOOP
Set @tabletodelete=null;
Select temptable INTO @tabletodelete from paginationHdr Where sessionid=p_sessionid And queryid = p_queryid And whereclause=p_whereclause limit 1;

If @tabletodelete is null Then
leave LoopStart;
END IF;

SET @s = CONCAT(‘Drop table ‘ , @tabletodelete, ‘;’ );

PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Delete from paginationHdr Where sessionid=p_sessionid And queryid = p_queryid And whereclause=p_whereclause And temptable=@tabletodelete;
END Loop;

Insert into paginationHdr Values (p_sessionid,p_queryid,@v_temptable, p_whereclause) ;

SET @s = CONCAT(‘Create table ‘ , @v_temptable , @v_basequery ,p_whereclause,’ order by ‘,p_orderby,’;’ );

PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @s = CONCAT(‘Update ‘ , @v_temptable , ‘ Set pg99= CASE WHEN MOD(rnk99,’,@v_pagesize,’)=0 Then rnk99 DIV ‘,@v_pagesize,’ WHEN MOD(rnk99,’,@v_pagesize,’) > 0 THEN rnk99 DIV ‘,@v_pagesize,’ +1 END ;’ );

PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;

SET @s = CONCAT(‘Select * from ‘ , @v_temptable , ‘ Where pg99=’ ,p_page,’;’ );

PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END//

 

 

Advertisements

Hadoop, Vertica, SSIS – Sandbox – Quick step by step


We are going to consider setting up a sandbox in a windows laptop (8.x or 10) to play around HP Vertica or to build a single node proof of concept.

Step 1. Download and install VMware player from vmware.com.  This is a virtualization tool which is free for non-commercial use.

Step 2: Download sandbox zip file. This is available free from HP marketplace. You need to create a free account in order to get access (https://haven.hpwsportal.com/catalog.html#/Product/%7B%22productId%22%3A%221826%22%7D/Show)

Once you download the 3 + GB zip file, unzip and place the ova file in your local windows machine.

Step 3:  Start vmware player and File->Open appliance, Then select the .ova sandbox image to import. This may take some time and will ask for server parameters such as memory, hard disk , processor etc.  Consider minimum needed and your resource availability. Once import is done, you can start/play the new virtual machine. This is a Centos image where Hadoop and Vertica are preconfigured.

Step 4: Once the system is started you will see black landing screen with an IP address mentioned there. Note that IP address. I will mention it as YourIP going forward in this document.

Step 5: Open a browser and navigate to http://YourIP . This will take you to a registration screen. Give the details and register yourself.

Step 6: Access Hue. Open a browser and navigate to http://YourIP:8000. The default userid/password is hue/1111

1

Step 7: Enable Ambari by clicking on the button “Enable” to the right side of “Ambari” in the list. Ambari will give you an insight on what is going on in the server.

http://YouIP:8080

2

Step 8: Connect Putty , give YourIP, port :22, SSH  . This will connect you to the linux machine. Login using root with password ‘hadoop’

Step 9: There is a default database created in vertica already “vive”. Start admin tool to initiate the database.

$ cd /opt/vertica/bin

$ su dbadmin

$ ./admintools

Use arrow keys and hit “Start Database”. Select the listed database (vive) by pressing spacebar which will print an ‘X’ next to VIVE. Give OK. When asked for password give ‘vive’ Wait until you see “Database vive started successfully”. You can go back to the admintools menu and see the database status by hitting on “View database Cluster state”

Hit exit to quit from the admin tools.

Step 10: You can start the demo by running the script available in the image.

$ cd /home/dbadmin/demo/vDemo

$ ./start_vDemo.sh

You can browse the demo database and query it using an online tool available.

http://YourIP:8111/home/hortonworks/hortonworks.html

3

Step 11: Connecting to Vertica from host Windows.

Download DBeaver database explorer tool which is available free to download.

Open a new connection , give YourIP as host , port:5433, database :vive

User :dbadmin

Password:vertica

4

Step 12: To load data from SQL Server using SSIS

Download Vertica ODBC Driver from My.Vertica.com. Register and Download windows ODBC 64 bit driver. Install the exe.

5

Open an SSIS package and connect Source SQL Server database, and setup transformation. You can create destination table using DBeaver. Select ODBC destination and create new dsn. Replace the IP with YourIP in the below screen. Map the Columns and Run the package.

6

7

Go to DBeaver and select the table. Good Luck.

Logical Table Editor


Logical table editor enable us to work with logical table and definition. It is one of the schema object editors available with Microstrategy Architect. While the physical view of the table explains the data types and columns , the Logical view of a table describes its attributes and facts association with a column in the table.

Logical table editor can be viewed at Microstrategy Desktop-> Schema objects -> Tables . Then in the right pane we will get all the logical tables. Double click on a table will display Logical table editor of it.

And the physical view tab