SparkSQL – Inserting and Loading Data

This post will cover how to work with tables, including how to create them, load data or insert values into them, and show the contents of the tables.

As stated earlier, please execute the below code in a single line to avoid each line from being executed separately. Or you can use triple-quotes “”” at the start and end of the SQL code or a backslash at the end of each line.

//////////////
Example-1:
//////////////
~~~~~~~~~~~~~~~
Creating a table:

sqlContext.sql(
“CREATE TABLE emp(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘,’
LINES TERMINATED BY ‘\n'”);

~~~~~~~~~~~~~~~~~~~~~~~~~
Loading data into the table:

sqlContext.sql(“
LOAD DATA LOCAL INPATH ‘Desktop/emp.csv’ INTO TABLE emp” )

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Let’s load the table data into a variable

val result = sqlContext.sql(“FROM emp SELECT ename”)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Display the contents of the variable.

result.show()

//////////////
Example-2:
//////////////

~~~~~~~~~~~~~~~
Creating a table:

sqlContext.sql(“
CREATE TABLE emptest(
empno int,
ename string,
locid int)”);

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Inserting data into the table:

sqlContext.sql(
“insert into table emptest select t.* from (select 101,’John’,10) t”)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Data can be inserted from data-frames using the below code.

var data = sqlContext.createDataFrame(Seq((101,”John”,10))).toDF(“empno”, “ename”, “locid”)

Now, write the data-frame data into a table:
data.write.insertInto(“emptest”)

Display the output:
val result = sqlContext.sql(“FROM emptest SELECT empno,ename,locid”)

result.show()