package de.hsma.informatik.bdea;

import static org.apache.spark.sql.functions.col;

import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Encoders;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession;

// https://github.com/apache/spark/blob/master/examples/src/main/java/org/apache/spark/examples/sql/JavaSparkSQLExample.java

public class BasicSQL {

	public static void main(String[] args) {
		SparkSession spark = SparkSession.builder()
				.appName("Spark SQL examples").master("local[*]").getOrCreate();

		Dataset<Row> data = spark.read().json("spark-it/src/main/resources/data.json");
		
		data.show();
		data.printSchema();

		Dataset<Row> fnameDS = data.select("fname");
		fnameDS.show();

		Dataset<Row> nameDS = data.select(col("fname"), col("lname"));
		nameDS.show();

		Dataset<Row> genderDS = data.groupBy("gender").count();
		genderDS.show();

		Dataset<Row> maleGenderDS = data.filter(col("gender").eqNullSafe("Male"));
		maleGenderDS.show();

		Dataset<Person> peopleDS = data.as(Encoders.bean(Person.class));
		peopleDS.show();

		System.out.println("Select...");
		data.createOrReplaceTempView("People");
		Dataset<Row> sqlDF = spark.sql("SELECT * FROM People WHERE lname like 'W%'");
		sqlDF.show();

		spark.close();
		spark.stop();
	}

}
