Spark SQL – load data with JDBC using SQL statement, not table name

by

You should pass a valid subquery as a dbtable argument. For example in Scala:

val query = """(SELECT TOP 1000 
  -- and the rest of your query
  -- ...
) AS tmp  -- alias is mandatory*"""   

val url: String = ??? 

val jdbcDF = sqlContext.read.format("jdbc")
  .options(Map("url" -> url, "dbtable" -> query))
  .load()

* Hive Language Manual SubQueries: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SubQueries

More Related Contents:

  1. Finding duplicates from large data set using Apache Spark
  2. Find maximum row per group in Spark DataFrame
  3. How to connect Spark SQL to remote Hive metastore (via thrift protocol) with no hive-site.xml?
  4. While writing to hdfs path getting error java.io.IOException: Failed to rename
  5. Multiple Aggregate operations on the same column of a spark dataframe
  6. How to access element of a VectorUDT column in a Spark DataFrame?
  7. How to optimize partitioning when migrating data from JDBC source?
  8. How to split a list to multiple columns in Pyspark?
  9. Filtering a spark dataframe based on date
  10. How to save/insert each DStream into a permanent table
  11. Spark DataFrame Schema Nullable Fields
  12. Spark load data and add filename as dataframe column
  13. PySpark: how to resample frequencies
  14. Why does Spark think this is a cross / Cartesian join
  15. PySpark – get row number for each row in a group
  16. How to find count of Null and Nan values for each column in a PySpark dataframe efficiently?
  17. Spark SQL broadcast hash join
  18. What is the difference between Apache Spark SQLContext vs HiveContext?
  19. What should be the optimal value for spark.sql.shuffle.partitions or how do we increase partitions when using Spark SQL?
  20. pyspark: count distinct over a window
  21. Spark lists all leaf node even in partitioned data
  22. Array Intersection in Spark SQL
  23. PySpark: How to fillna values in dataframe for specific columns?
  24. How to get Kafka offsets for structured query for manual and reliable offset management?
  25. How to use groupBy to collect rows into a map?
  26. When to use Spark DataFrame/Dataset API and when to use plain RDD?
  27. How to calculate Median in spark sqlContext for column of data type double
  28. Spark: disk I/O on stage boundaries explanation
  29. spark.ml StringIndexer throws ‘Unseen label’ on fit()
  30. PySpark error: AttributeError: ‘NoneType’ object has no attribute ‘_jvm’

Leave a Comment