踩坑事件：不能对基于文本的临时表使用sql insert语句

先来描述一下问题：

  如果你是从基于文本的数据源来创建DataFrame的，当你将DataFrame注册为临时表后，如果对这个临时表进行insert into 操作，会抛出异常的。

问题答案参见：http://apache-spark-user-list.1001560.n3.nabble.com/How-to-direct-insert-vaules-into-SparkSQL-tables-td11851.html

no, spark sql can not insert or update textfile yet, can only insert into parquet files 

but, 

people.union(new_people).registerAsTable("people") 

could be an idea.

 

 

后来再对基于parquet的DataFrame进行insert into 操作时也出问题，后来发现从这里找到了答案：

http://stackoverflow.com/questions/33923348/insert-into-with-sparksql-hivecontext

 

原因就是语法不对。

原来的语法:insert into people(age,name) values (10,'francis')

修改后的语法：insert into table people select t.* from (select 10,'francis') t

 

        // 首先还是创建SparkConf
        SparkConf conf = new SparkConf()
                .setMaster("local")
                .setAppName("HiveDataSource");
        // 创建JavaSparkContext
        JavaSparkContext sc = new JavaSparkContext(conf);
       SQLContext sqlContext=new SQLContext(sc);
 
     DataFrame peopleDF=sqlContext.read().parquet("hdfs://spark2:9000/francis/spark-core/people2.parquet");
     
     peopleDF.show();
  
     peopleDF.registerTempTable("people");
     
      qlContext.sql("insert into table people select t.* from (select 25,'francis') t"); 
     //sqlContext.sql("insert into table people (age,name) values (25,'francis')");  错误
     
     
     peopleDF.show();

 

 

up vote2down vote

I've had the same problem (Spark 1.5.1), and tried different versions. Given sqlContext.sql("create table my_table(id int, score int)") The only versions that worked looked like this: sqlContext.sql("insert into table my_table select t.* from (select 1, 10) t") sqlContext.sql("insert into my_table select t.* from (select 2, 20) t") shareimprove this answer