title: Connect to SQLite in Apache Spark
body: <p>I want to run a custom function on all tables in a SQLite database. The function is more or less the same, but depends on the schema of the individual table. Also, the tables and their schemata are only known at runtime (the program is called with an argument that specifies the path of the database).</p>
  
  <p>This is what I have so far:</p>
  
  <pre><code>val conf = new SparkConf().setAppName("MyApp")
  val sc = new SparkContext(conf)
  val sqlContext = new org.apache.spark.sql.SQLContext(sc)
  
  // somehow bind sqlContext to DB
  
  val allTables = sqlContext.tableNames
  
  for( t &lt;- allTables) {
      val df = sqlContext.table(t)
      val schema = df.columns
      sqlContext.sql("SELECT * FROM " + t + "...").map(x =&gt; myFunc(x,schema))
  }
  </code></pre>
  
  <p>The only hint I found so far needs to know the table in advance, which is not the case in my scenario:</p>
  
  <pre><code>val tableData = 
    sqlContext.read.format("jdbc")
      .options(Map("url" -&gt; "jdbc:sqlite:/path/to/file.db", "dbtable" -&gt; t))
      .load()
  </code></pre>
  
  <p>I am using the xerial sqlite jdbc driver. So how can I conntect solely to a database, not to a table?</p>
  
  <p>Edit: Using Beryllium's answer as a start I updated my code to this:</p>
  
  <pre><code>val sqlContext = new org.apache.spark.sql.SQLContext(sc)
  
  val metaData = sqlContext.read.format("jdbc")
      .options(Map("url" -&gt; "jdbc:sqlite:/path/to/file.db",
                   "dbtable" -&gt; "(SELECT * FROM sqlite_master) AS t")).load()
  
  val myTableNames = metaData.select("tbl_name").distinct()
  
  for (t &lt;- myTableNames) {
      println(t.toString)
  
      val tableData = sqlContext.table(t.toString)
  
      for (record &lt;- tableData.select("*")) {
          println(record)
      }
  }
  </code></pre>
  
  <p>At least I can read the table names at runtime which is a huge step forward for me. But I can't read the tables. I tried both</p>
  
  <pre><code>val tableData = sqlContext.table(t.toString)
  </code></pre>
  
  <p>and</p>
  
  <pre><code>val tableData = sqlContext.read.format("jdbc")
      .options(Map("url" -&gt; "jdbc:sqlite:/path/to/file.db",
                   "dbtable" -&gt; t.toString)).load()
  </code></pre>
  
  <p>in the loop, but in both cases I get a NullPointerException. Although I can print the table names it seems I cannot connect to them.</p>
  
  <p>Last but not least I always get an <code>SQLITE_ERROR: Connection is closed</code> error. It looks to be the same issue described in this question: <a href="https://stackoverflow.com/questions/32860100/sqlite-error-connection-is-closed-when-connecting-from-spark-via-jdbc-to-sqlite">SQLITE_ERROR: Connection is closed when connecting from Spark via JDBC to SQLite database</a> </p>