<jupyter_start><jupyter_text>Create a new spark session<jupyter_code>from pyspark.sql import SparkSession
spark = SparkSession.builder.appName("SparkSQL").getOrCreate()<jupyter_output><empty_output><jupyter_text>Read data<jupyter_code>data = spark.read.csv('investments.csv',inferSchema=True, header=True)
data.show()
data.columns
data.printSchema()<jupyter_output>root
 |-- permalink: string (nullable = true)
 |-- name: string (nullable = true)
 |-- homepage_url: string (nullable = true)
 |-- category_list: string (nullable = true)
 |--  market : string (nullable = true)
 |--  funding_total_usd : string (nullable = true)
 |-- status: string (nullable = true)
 |-- country_code: string (nullable = true)
 |-- state_code: string (nullable = true)
 |-- region: string (nullable = true)
 |-- city: string (nullable = true)
 |-- funding_rounds: integer (nullable = true)
 |-- founded_at: timestamp (nullable = true)
 |-- founded_month: string (nullable = true)
 |-- founded_quarter: string (nullable = true)
 |-- founded_year: integer (nullable = true)
 |-- first_funding_at: timestamp (nullable = true)
 |-- last_funding_at: timestamp (nullable = true)
 |-- seed: integer (nullable = true)
 |-- venture: long (nullable = true)
 |-- equity_crowdfunding: integer (nullable = true)
 |-- undisclosed: integer (nullable = true)
 |-- convertible_note: integer (nullable[...]<jupyter_text>Create a temprary table This is an essential step to use a SQL query<jupyter_code>data.createOrReplaceTempView('investment')
spark.sql("select * from investment limit 10").show()
spark.sql("select * from investment where country_code == 'USA'").show()
usstrartsup = spark.sql("select * from investment where country_code == 'USA'")
usstrartsup.show()
usstrartsup.createOrReplaceTempView('usstartup_table')
spark.sql("select * from usstartup_table limit 10").show()<jupyter_output>+--------------------+------------------+--------------------+--------------------+--------------------+-------------------+---------+------------+----------+--------------------+-----------------+--------------+-------------------+-------------+---------------+------------+-------------------+-------------------+-------+-------+-------------------+-----------+----------------+--------------+-----+-----+--------------+---------------+-------------+----------------+--------------------+-------+-------+-------+-------+-------+-------+-------+-------+
|           permalink|              name|        homepage_url|       category_list|             market | funding_total_usd |   status|country_code|state_code|              region|             city|funding_rounds|         founded_at|founded_month|founded_quarter|founded_year|   first_funding_at|    last_funding_at|   seed|venture|equity_crowdfunding|undisclosed|convertible_note|debt_financing|angel|grant|private_equity|post_ipo_equity|post_ip[...]<jupyter_text>Find most seeded startup in USA<jupyter_code>most_seed = spark.sql("select * from usstartup_table order by seed desc")
most_seed.select('name','seed').show()
most_seed.head(1)
most_seed_startup = most_seed.head(1)[0].asDict()
most_seed_startup<jupyter_output><empty_output><jupyter_text>Only row objects can be converted to dict. by converting a row to dictionary indexing becomes easier.<jupyter_code>most_seed_startup['venture']
most_seed_startup['first_funding_at']<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.2706299911268855, 'fraction_numerical': 0.002070393374741201, 'mean_word_length': 2.5119418483904465, 'pattern_counts': {'":': 0, '<': 19, '<?xml version=': 0, '>': 19, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '79312', 'license': 'MIT', 'n_tokens_mistral': 975, 'n_tokens_neox': 957, 'n_words': 192, 'path': 'Course_4-Big_Data_Processing_using_Apache_Spark/Module_2-Spark_Structured_APIs/1-Introduction_to_Structured_APIs/Spark_SQL_Notebook/SparkSQL.ipynb', 'repo_name': 'Mathews-Tom/MSc_in_Machine_Learning_and_Artificial_Intelligence', 'size': '3381'}