from pyspark sql types import fields [structfield(field_name,stringtype(),truefor field_name in firstlineschema structtype(fieldsschemaloans sqlcontext createdataframe(datalinesschemaschemaloans registertemptable("loans"with the metadata readywe're now able to insert the data into hive step execute queries and store table in hive now we're ready to use sql-dialect on our data first we'll make summary table that counts the number of loans per purpose then we store subset of the cleaned raw data in hive for visualization in qlik executing sql-like commands is as easy as passing string that contains the sqlcommand to the sqlcontext sql function notice that we aren' writing pure sql because we're communicating directly with hive hive has its own sql-dialect called hiveql in our sqlfor instancewe immediately tell it to store the data as parquet file parquet is popular big data file format sqlcontext sql("drop table if exists loansbytitle"sql '''create table loansbytitle stored as parquet as select titlecount( as number from loans group by title order by number desc''sqlcontext sql(sqlsqlcontext sql('drop table if exists raw'sql '''create table raw stored as parquet as select titleemp_title,grade,home_ownership,int_rate,recoveries,collection_recovery_f ee,loan_amnt,term from loans''sqlcontext sql(sqlwith the data stored in hivewe can connect our visualization tools to it step data exploration step report building we'll build an interactive report with qlik sense to show to our manager qlik sense can be downloaded from subscribing to their website when the download begins you will be redirected to page containing several informational videos on how to install and work with qlik sense it' recommended to watch these first we use the hive odbc connector to read data from hive and make it available for qlik tutorial on installing odbc connectors in qlik is available for major operating systemsthis can be found at note in windowsthis might not work out of the box once you install the odbcmake sure to check your windows odbc manager (ctrl+ and look for odbcin the managergo to "system-dsnand select the "sample hive hortonworks dsnmake sure your settings are correct (as shown in figure or qlik won' connect to the hortonworks sandbox