package SQL
import org.apache.spark.SparkContext
import org.apache.spark.sql._
/**
* Created by devan on 21/11/14.
* mail msdevanms@gmail.com
*/
object SparSQLCSV {
def main(args: Array[String]) {
val sc = new SparkContext("local[*]","home")
val sqlContext = new org.apache.spark.sql.SQLContext(sc)
val people = sc.textFile("/home/devan/Documents/dataset/peoplesTest.csv")
val delimiter = ","
val schemaString = "a,b".split(delimiter)//csv header
//Automated Schema creation
val schema = StructType(schemaString.map(fieldName => StructField(fieldName, StringType, true)))
val peopleLines = people.flatMap(x=> x.split("\n"))
val rowRDD = peopleLines.map(p=>{
Row.fromSeq(p.split(delimiter))
})
val peopleSchemaRDD = sqlContext.applySchema(rowRDD, schema)
peopleSchemaRDD.registerTempTable("people")
sqlContext.sql("SELECT b FROM people").foreach(println)
}
}
What's the difference between RDD's map,FlatMap and mapPartitions method?
map converts each element of the source RDD into a single element of the result RDD by applying a function. mapPartitions converts each partition of the source RDD into into multiple elements of the result (possibly none). FlatMap works on a single element (as map) and produces multiple elements of the result (as mapPartitions).a.map(r => r * 2) a.mapPartitions(iter => iter.map(r => r *2))
import java.sql.SQLException;import java.sql.Connection;import java.sql.ResultSet;import java.sql.Statement;import java.sql.DriverManager;public class HiveJdbcClient { private static String driverName = "org.apache.hadoop.hive.jdbc.HiveDriver"; /** * @param args * @throws SQLException */ public static void main(String[] args) throws SQLException { try { Class.forName(driverName); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); System.exit(1); } Connection con = DriverManager.getConnection("jdbc:hive://localhost:10000/default", "", ""); Statement stmt = con.createStatement(); String tableName = "testHiveDriverTable"; stmt.executeQuery("drop table " + tableName); ResultSet res = stmt.executeQuery("create table " + tableName + " (key int, value string)"); // show tables String sql = "show tables '" + tableName + "'"; System.out.println("Running: " + sql); res = stmt.executeQuery(sql); if (res.next()) { System.out.println(res.getString(1)); } // describe table sql = "describe " + tableName; System.out.println("Running: " + sql); res = stmt.executeQuery(sql); while (res.next()) { System.out.println(res.getString(1) + "\t" + res.getString(2)); } // load data into table // NOTE: filepath has to be local to the hive server // NOTE: /tmp/a.txt is a ctrl-A separated file with two fields per line String filepath = "/tmp/a.txt"; sql = "load data local inpath '" + filepath + "' into table " + tableName; System.out.println("Running: " + sql); res = stmt.executeQuery(sql); // select * query sql = "select * from " + tableName; System.out.println("Running: " + sql); res = stmt.executeQuery(sql); while (res.next()) { System.out.println(String.valueOf(res.getInt(1)) + "\t" + res.getString(2)); } // regular hive query sql = "select count(1) from " + tableName; System.out.println("Running: " + sql); res = stmt.executeQuery(sql); while (res.next()) { System.out.println(res.getString(1)); } }}