Change Hive metastore from derby to MySQL

 

Change Hive metastore from derby to MySQL

 

Machine : UBUNTU-14.04 |  Hive :  HIve 1.2.1

To change Hive Metastore from Derby to MySQL we need to follow these 8 simple steps,

 

Step-1 :

First we need to install Mysql and its dependencies on system.

Command-1 : sudo apt-get install mysql-server

Note: Click Next > Next and set the password for MySQL.

Command-2 :  sudo apt-get install libmysql-java

 

Step-2 :

Create soft-link for connector in Hive lib directory.

Command-1 : ln -s /usr/share/java/mysql-connector-java.jar $HIVE_HOME/lib/mysql-connector-java.jar

 

Step-3 :

Access your MySQL and create one new database metastore for hive,

Command : $ mysql -u root -p

Enter password:

mysql> CREATE DATABASE metastore;

 

Step-4 :

Then we need one MySQL account for Hive to access the metastore. It is very important to prevent this user account from any type of change in schema.

Command :

mysql> CREATE USER ‘hive’@’%’ IDENTIFIED BY ‘password’;

mysql> GRANT all on *.* to ‘hive’@localhost identified by ‘password’;

mysql>  flush privileges;

 

Step-5 :

Now we need to configure Hive to access MySQL metastore, for this we need to update hive-site.xml file(If file does not exist then create a new one)

<configuration>

<property>

<name>javax.jdo.option.ConnectionURL</name>

<value>jdbc:mysql://192.168.8.99/metastore?createDatabaseIfNotExist=true</value>

<description>metadata is stored in a MySQL server</description>

</property>

<property>

<name>javax.jdo.option.ConnectionDriverName</name>

<value>com.mysql.jdbc.Driver</value>

<description>MySQL JDBC driver class</description>

</property>

<property>

<name>javax.jdo.option.ConnectionUserName</name>

<value>hive</value>

<description>user name for connecting to mysql server</description>

</property>

<property>

<name>javax.jdo.option.ConnectionPassword</name>

<value>password</value>

<description>password for connecting to mysql server</description>

</property>

</configuration>

 

Note: While updating please take all properties-tag only.

 

Step-6 :

Now we need to run the Hive schematool to initialization MySQL metastore.

For this we need to go to $HIVE_HOME>bin> folder

Command-1 : schematool -initSchema -dbType mysql

Note : When you have found that your metastore is corrupted, then we need to update metastore.

  • Before you run hive for the first time, run

Command : schematool -initSchema -dbType mysql

  • If you already ran hive and then tried to initSchema and if it’s failing:

Command : mv metastore_db metastore_db.tmp

You find your metasore_db file at $HIVE_HOME location.

  • Re run

 

Step-7 :

Start your Hive and access your tables.

 

Step-8 :

To validate it ,

Connect and open your hive

hive>

Then create a table in it and insert one record.

hive> create table saurzcode(id int, name string);

hive> insert into saurzcode(1, “Helical”);

Later access your MySQL and open metastore database

mysql -u root -p

Enter password:

mysql> use metastore;

And see your table as a record in TBLS table of metastore database.

mysql> show tables ;

mysql> select * from TBLS;

 

Hurray, Completed !!!!

 

Thanks for visiting , Have a great day.

PUSHPRAJ KUMAR 

The Print When Expression

The Print When Expression

The print when expression in Jasper Studio or Jasper iReport is very useful in so many requirements and it is defined as the name itself implies
i.e Print /Show a String, Column and so on Based on a condition passed.
Now here are some requirements on how/when we can implement The Print when expression.

Requiremnt: Show a particular column when a parameter is selected
Solution:
Here we made a parameter Called ShowLocation with values either ‘Y’ or ‘N’/blank space
now when ‘Y’ is selected then a Location column should show and when ‘N/blank space then it doesn’t show.

2 ways i did approach this:

1. I made a 2 Table Components one with the Location Column added and the other without the Location Column added.
one on top the other.
so when my parameter calls a ‘Y’ value it picks the Table with the Location Column and vice versa.

This approach is good but can be time and performance consuming

the 2nd approach was:

2. Only one Table Component was used, Opened the Table Component and selected the Column i want to restrict/show based on the parameter passed, in my scenario or requirement it is the Location column as
shown in the image below:

TableLayout

now i would select the cell button

Cellin the properties Tab and then Select the
Print When Expression.

Now here in the print when expression box i would write :
PrintExperssion
$P{showlocation}.equals(“Y”) as shown above

 

and you are good to go for a preview.
Now Let’s have to put a N or blank space in the parameter showlocation we get the following output
before
and now let’s pass ‘Y’ in the showlocation parameter

before-Y
So Now with this condition it will show the column when the parameter is checked in with a ‘Y’ Value.
and voila the output:

after

Thanks
Sohail Izebhijie.

MySql case sensitive table names issue on Windows/Linux

MySql case sensitive table names issue on Windows/LinuxTableNameIssue


 

Moving a MySQL database from Windows to Linux .Many times we faced the problem that on Linux the names of the tables are case sensitive due to that we can’t find tables.

Solution :-

Note:

Just altering the “lower_case_table_names” setting isn’t enough. It needs to be done before you import your database(s).

Location:

/etc/mysql/my.cnf file (add the row lower_case_table_names=1)

Note:

lower_case_table_names=0 >  table names are stored as specified and comparisons are case sensitive.

lower_case_table_names=1 >   table names are stored in lowercase on disk and comparisons are not case sensitive.

lower_case_table_names=2 >   table names are stored as given but compared in lowercase.

This option also applies to database names and table aliases.

 

The MySQL 5.1 documentation lists a procedure for moving between Windows and Linux/UNIX. This will ensure that your desired rules for enforcing case sensitivity are followed. Take a look and verify that you did these steps in the correct order:

 

To convert one or more entire databases, dump them before setting lower_case_table_names, then drop the databases, and reload them after setting lower_case_table_names:

 

Step- 1

Use mysqldump to dump each database:

mysqldump –databases db1 > db1.sql

mysqldump –databases db2 > db2.sql

… Do this for each database that must be recreated.

 

Step -2

Use DROP DATABASE to drop each database.

 

Step -3

Stop the mysql-server, set lower_case_table_names in the [mysqld] section of your \etc\mysql\my.cnf file, and restart the server.

 

Step-4

Reload the dump file for each database. Because lower_case_table_names is set, each database and table name will be converted to lowercase as it is recreated:

mysql < db1.sql

mysql < db2.sql

Thanks for reading : Pushpraj Kumar ( Helical IT Solutions )

MySQL C – API programming

MySQL C – API programming

MySQL is a leading open source database management system. It is a multi user, multithreaded database management system. MySQL is especially popular on the web.

Basic Structure of C Programs that uses MySQL C API 1. All programs must include <mysql/mysql.h> as the last include.2. Define MYSQL type variable. NOTE: THERE CAN BE ONLY ONE MYSQL VARIABLE.3. Initialize MYSQL type variable with mysql_init()4. Load any options, if required, by using mysql_options(). If you don’t need don’t call. You can call this fuction multiple times if you require. If you call this, call this before mysql_real_connect() and after mysql_init().5. Connect by calling mysql_real_connect()

6. Call the business logic and MySQL API’s

7. Close the MYSQL type variable.

mysqlwithc

 

Template of program

#include <mysql/mysql.h><return_type> <function_name>(parameters)

{

MYSQL mysql; mysql_init(&mysql);

mysql_options(&mysql,MYSQL_OPT_COMPRESS,0);      

/*call only if required otherwise omit*/

mysql_options(&mysql,MYSQL_READ_DEFAULT_GROUP,”Pushpraj”);        

 /*call only if required otherwise omit*/

mysql_real_connect(….);      

/* now call other API’s*/

mysql_close(&mysql);

}

 Compiling and Running in UNIX :- 

To Compile :-  $gcc mysql_app.c -o mysql_app -I/usr/local/include -L/usr/local/lib/mysql –lmysqlclient

To Run :-  $./mysql_app

Examples:-

1. Test the connection.

#include </usr/include/mysql/my_global.h>

#include </usr/include/mysql/mysql.h>

int main(int argc, char **argv)

{

printf(“MySQL client version: %s\n”, mysql_get_client_info());

  exit(0);

}

Description:-  “mysql_get_client_info()”, this mysql function collects the version information of mysql.

2. Create table and insert data into table.

#include <my_global.h>

#include <mysql.h>

void finish_with_error(MYSQL *con){

  fprintf(stderr, “%s\n”, mysql_error(con));

  mysql_close(con);

  exit(1);       

}

 

int main(int argc, char **argv)

{

  MYSQL *con = mysql_init(NULL);

  if (con == NULL) {

      fprintf(stderr, “%s\n”, mysql_error(con));

      exit(1);

  } 

if (mysql_real_connect(con, “192.168.2.50”, “username”, “password”,”db-Name”, port-No, NULL, 0) == NULL)

  {

      finish_with_error(con);

  }   

 

  if (mysql_query(con, “DROP TABLE IF EXISTS Dell”)) {

      finish_with_error(con);

  }

  if (mysql_query(con, “CREATE TABLE Dell(Id INT, Name TEXT, Price INT)”)) {     

      finish_with_error(con);

  }

  if (mysql_query(con, “INSERT INTO Dell VALUES(1,’vostro14′,26042)”)) {

      finish_with_error(con);

  }

  if (mysql_query(con, “INSERT INTO Dell VALUES(2,’inspiron15′,37000)”)) {

      finish_with_error(con);

  }

  if (mysql_query(con, “INSERT INTO Dell VALUES(3,’inspiron16′,40000)”)) {

      finish_with_error(con);

  }

  if (mysql_query(con, “INSERT INTO Dell VALUES(4,’ vostro15′,29000)”)) {

      finish_with_error(con);

  }

  if (mysql_query(con, “INSERT INTO Dell VALUES(5,’express’,69000)”)) {

      finish_with_error(con);

  }

  mysql_close(con);

  exit(0);

}

Description:- mysql_init, allocates/intialises a Mysql object suitable for mysql_real_connect()establishes a connection to the database.We provide connection handler, host name, username ,password ,database name, port number , unix socket and client flag.

3. Create connection with Database & fetch the records.

#include <my_global.h>

#include <mysql.h>

void finish_with_error(MYSQL *con)

{

  fprintf(stderr, “%s\n”, mysql_error(con));

  mysql_close(con);

  exit(1);       

}

int main(int argc, char **argv)

{     

  MYSQL *con = mysql_init(NULL);

  if (con == NULL)

  {

      fprintf(stderr, “mysql_init() failed\n”);

      exit(1);

  } 

 

  if (mysql_real_connect(con, “192.168.2.50”, “username”, “password”,”db-Name”, port-No, NULL, 0) == NULL)

  {      finish_with_error(con);  }  

 

  if (mysql_query(con, “SELECT * FROM Dell”))

  {      finish_with_error(con);  }  

  MYSQL_RES *result = mysql_store_result(con);

  if (result == NULL)

  {      finish_with_error(con);  }  

  int num_fields = mysql_num_fields(result);

  MYSQL_ROW row;

  while ((row = mysql_fetch_row(result)))

  {

      for(int i = 0; i < num_fields; i++)

      {

          printf(“%s “, row[i] ? row[i] : “NULL”);

      }

      printf(“\n”);

  }

  mysql_free_result(result);

  mysql_close(con);

  exit(0);

}

Description:-

  • Create a connection:: mysql_real_connect(con, “192.168.2.50”, “username”, “password”,”db-Name”, port-No, NULL, 0)
  • Execute query:: mysql_query(con, “SELECT * FROM Dell”)
  • Get the result set:: MYSQL_RES *result = mysql_store_result(con)
  • Fetch all available rows one by one :: row = mysql_fetch_row(result)
  • Free the result set:: mysql_free_result(result)
  • Close the connection:: mysql_close(con);

Thanks

Pushpraj Kumar

Finding MySQL Database Size

Finding MySQL Database Size

Here’s the SQL script to list out the entire databases size.

SELECT table_schema "Data Base Name",
SUM( data_length + index_length) / 1024 / 1024 "Data Base Size in MB"
FROM information_schema.TABLES
GROUP BY table_schema;
SELECT TABLE_NAME, table_rows, data_length, index_length,
round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
FROM information_schema.TABLES
WHERE table_schema = "schema_name";

Following is the calculation:

data_length + index_length = total table size.

data_length = store the real data.
index_length = store the table index.

CREATE EVENT IN MYSQL AND SOME ADVANCED MYSQL COMMANDS

Dear Readers, this blog will be talking about how to create even in MySQL and some advanced MySQL commands. To create event in MySQL, we have to follow a Pattern which is written below..

1. Declare Delimiter

2. Define Name of the event

3. Define when to schedule

4. Start with “DO”

5. Then “Begin”

6. Define Business logic

(Like variable declaration, job which you want to schedule through your Event, any condition…So mainly it is the body of your Event)

7. Then Declare end of your event like “END <delimiter>”

8. Change Delimiter to normal Delimiter.

Example:- Here I tried to call a stored procedure in this event which is scheduled after every 4-Hour, and passed the parameter for that stored procedure by taking two date parameter, and also with some additional parameters, I tried to use loop and if-else condition also in this event…     (Tested and executed Event)

delimiter $$

CREATE EVENT ue_schedule_test

ON SCHEDULE

EVERY 4 HOUR

DO

BEGIN

DECLARE to_temp TEXT(25);

DECLARE from_temp TEXT(25);

DECLARE pv_temp TEXT(20);

DECLARE done INT DEFAULT FALSE;

DECLARE curs1 CURSOR FOR SELECT name FROM data ;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN curs1;

read_loop: LOOP

FETCH curs1 INTO pv_temp;

SELECT DATE_FORMAT(DATE_ADD(convert_tz(CURDATE(),'SYSTEM','+00:00'),INTERVAL -4 HOUR),'%Y-%m-%d %H:%i:%S') INTO from_temp;

SELECT DATE_FORMAT(DATE_ADD(convert_tz(CURDATE(),'SYSTEM','+00:00'),INTERVAL 4 HOUR),'%Y-%m-%d %H:%i:%S') INTO to_temp;

DELETE FROM availibility WHERE Date=from_temp;

CALL usp_availability_test(from_temp,to_temp,pv_temp,'Total','+00:00','custom');

IF done THEN

LEAVE read_loop;

END IF;

END LOOP;

CLOSE curs1;

END $$

delimiter ;

Some Other Important My-Sql Commands
1. To clear console — \! clear
2. To delete procedure — drop prcedure
3. Show all stored procedure — show procedure status
4. To get 2nd highest salary —SELECT DISTINCT(Salary) FROM employee ORDER BY Salary DESC LIMIT 1,1
5. To get 3rd highest salary —SELECT DISTINCT(Salary) FROM employee ORDER BY Salary DESC LIMIT 2,1
6. To convert string into datetime type —SELECT STR_TO_DATE(yourdatefield, ‘%m/%d/%Y’) FROM
7. To check event scheduler is ON/OFF — select @@event_scheduler
8. To start event-scheduler — set GLOBAL event_scheduler=ON
9. To delete duplicate records from table
— delete from table1 USING table1, table1 as vtable
WHERE table1.ID<vtable.ID AND table1.field_name=vtable.field_name;
So, These are the some Advanced My-SQL features , which may help you…

Have a Good Day………!!!
PUSHPRAJ KUMAR
Helical IT Solutions

Creating a Federated Tables inside MySQL

What is Federated Table?

A federated database system is a type of meta-database management system (DBMS), which transparently maps multiple autonomous database systems into a single federated database.

A Federated Table is a table which points to a table in another MySQL database instance (mostly on another server). It can be seen as a view to this remote database table. Other RDBMS have similar concepts for example database links.

federated table

Federated Tables Concept In MYSQL(linux) :

  1. Federated Storage Engine : Using Federated Storage Engine,we will be able to access data from tables of Remote databases rather than local databases. This Federated Engine concept is available from MYSQL 5.3.0 Onwards

Example : Suppose you want to access some data from the DataBase that is on one server1,

Also another set of data from different server2, but data on these two servers are not accessible through out the world , Rather these are available only on one server3 , so what you can do is :

Simply create “Federated” tables for all those tables from which you want to access data on server3 from server1 & server2. When using a FEDERATED table, queries on the server3 are automatically executed on the remote (federated) tables. No data is stored on the local tables.

NOTE :  By default Federated Storage Engine will not enabled on the mysql

Federated Table 2But if you want to verify whether federated engine is on or off, “SHOW ENGINES” sqlquery can help to identify the status.

federated table 2If value of Support column is “YES” then Federated Engine is Turned On, Else If it is “No” it means it is Turned Off.

To enable federated engine ,

  1. You add the line ‘federated’ to the /etc/my.cnf file after  [mysqld] section
  2. restart the mysql server

Federated Table 1

  1. Federated Table Creation : It has two elements :
    1. Remote server : Data is physically stored in this server
    2. Local Server :      No data is stored here,simply you can fire the query and you will be able to see your required output.

Example :

  1. Suppose you have a table on the Remote Server (say Master Table : “student_test”)

Data Base Name is :  STUDENT_DB that you want to   access by using a Federated table

 

CREATE TABLE student_test(

sid     INT(20) NOT NULL AUTO_INCREMENT,

sname   VARCHAR(32) NOT NULL DEFAULT ,

PRIMARY KEY  (id),

)ENGINE=MyISAM

DEFAULT CHARSET=latin1;

 

2. Next,You have to create a table on the local database server(say Federated Table :”Student_federated)” DataBase Name is STUDENT_FEDERATED to access the data on the Master Table

 

CREATE TABLE Student_federated(

sid     INT(20) NOT NULL AUTO_INCREMENT,

sname   VARCHAR(32) NOT NULL DEFAULT ,

PRIMARY KEY  (id),

)ENGINE=FEDERATED

DEFAULT CHARSET=latin1;

CONNECTION=’scheme://username:[email protected]:port/DBname/TableName;

For the above scenario , The connection String should be :

CONNECTION=’mysql://root:[email protected]:3306/STUDENT_DB /Student_test;

 

Sequence of execution:

– When a client issues an SQL statement that refers to a FEDERATED table, the flow of information between the local server (where the SQL statement is executed) and the remote server (where the data is physically stored) is as follows:

– The storage engine looks through each column that the FEDERATED table has and constructs an appropriate SQL statement that refers to the remote table.

– The statement is sent to the remote server using the MySQL client API.

– The remote server processes the statement and the local server retrieves any result that the statement produces (an affected-rows count or a result set).

If the statement produces a result set, each column is converted to internal storage engine format that the FEDERATED engine expects and can use to display the result to the client that issued the original statement.

 

Now You are able to get your required output by firing sql query

 

Thankyou

Helical IT Solutions