title: How to extract tables with data from .sql dumps using Spark?
body: <p>I have around four *.sql self-contained dumps ( about 20GB each)  which I need to convert to datasets in Apache Spark. </p>
  
  <p>I have tried installing and making a local database using InnoDB and importing the dump but that seems too slow ( spent around 10 hours with that ) </p>
  
  <p>I directly read the file into spark using</p>
  
  <pre><code>import org.apache.spark.sql.SparkSession
  
  var sparkSession = SparkSession.builder().appName("sparkSession").getOrCreate()
  var myQueryFile = sc.textFile("C:/Users/some_db.sql")
  
  //Convert this to indexed dataframe so you can parse multiple line create / data statements. 
  //This will also show you the structure of the sql dump for your usecase.
  
  var myQueryFileDF = myQueryFile.toDF.withColumn("index",monotonically_increasing_id()).withColumnRenamed("value","text") 
  
  
  // Identify all tables and data in the sql dump along with their indexes
  
  var tableStructures = myQueryFileDF.filter(col("text").contains("CREATE TABLE"))
  var tableStructureEnds = myQueryFileDF.filter(col("text").contains(") ENGINE"))
  
  println(" If there is a count mismatch between these values choose different substring "+ tableStructures.count()+ " " + tableStructureEnds.count())
  
  var tableData = myQueryFileDF.filter(col("text").contains("INSERT INTO "))
  </code></pre>
  
  <p>The problem is that the dump contains multiple tables as well each of which needs to become a dataset. For which I need to understand if we can do it for even one table. Is there any .sql parser written for scala spark ?</p>
  
  <p>Is there a faster way of going about it? Can I read it directly into hive from .sql self-contained file?</p>
  
  <p>UPDATE 1: I am writing the parser for this based on Input given by Ajay </p>
  
  <p>UPDATE 2: Changing everything to dataset based code to use SQL parser as suggested</p>