61-carga-dados-nome-sexo-pyspark.ipynb

{"cells":[{"cell_type":"markdown","source":["## Overview\n\nThis notebook will create and query a table or DataFrame that you uploaded to DBFS. [DBFS](https://docs.databricks.com/user-guide/dbfs-databricks-file-system.html) is a Databricks File System that allows you to store data for querying inside of Databricks. This notebook assumes that you have a file already inside of DBFS that you would like to read from.\n\nThis notebook is written in **Python** so the default cell type is Python. However, you can use different languages by using the `%LANGUAGE` syntax. Python, Scala, SQL, and R are all supported.\n\nhttps://docs.databricks.com/spark/latest/dataframes-datasets/introduction-to-dataframes-python.html"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"96816ed7-b08a-4ca3-abb9-f99880c3535d"}}},{"cell_type":"code","source":["# File location and type\nfile_location = \"/FileStore/tables/cadastro_nomes_sexo.csv\"\nfile_type = \"csv\"\n\n# CSV options\ninfer_schema = \"false\"\nfirst_row_is_header = \"true\"\ndelimiter = \",\"\n\n# The applied options are for CSV files. For other file types, these will be ignored.\ndf = spark.read.format(file_type) \\\n  .option(\"inferSchema\", infer_schema) \\\n  .option(\"header\", first_row_is_header) \\\n  .option(\"sep\", delimiter) \\\n  .load(file_location)\n\ndisplay(df)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"6482be4c-f067-47c9-b0ac-35c938b94601"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"code","source":["# Create a view or table\n\ntemp_table_name = \"cadastro_nomes_sexo\"\n\ndf.createOrReplaceTempView(temp_table_name)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"bd82bb99-1479-4d5c-be10-8c36df0f1d44"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\"></div>","removedWidgets":[],"addedWidgets":{},"type":"html","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}}],"execution_count":0},{"cell_type":"code","source":["%sql\n\n/* Query the created temp table in a SQL cell */\n\nselect * from `cadastro_nomes_sexo` limit 100"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"b5f66379-6f7f-42ec-8e82-d0e0926a1721"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"overflow":false,"datasetInfos":[],"data":[["MARIA","F"],["JOSE","M"],["ANA","F"],["JOAO","M"],["CARLOS","M"],["PAULO","M"],["ANTONIO","M"],["LUIZ","M"],["FRANCISCO","M"],["MARCELO","M"],["MARCOS","M"],["ANDRE","M"],["RODRIGO","M"],["RAFAEL","M"],["RICARDO","M"],["FERNANDO","M"],["PEDRO","M"],["EDUARDO","M"],["FERNANDA","F"],["PATRICIA","F"],["DANIEL","M"],["ALEXANDRE","M"],["JULIANA","F"],["BRUNO","M"],["MARCIA","F"],["FABIO","M"],["LEONARDO","M"],["LUCIANA","F"],["SERGIO","M"],["LUIS","M"],["JORGE","M"],["CLAUDIA","F"],["ADRIANA","F"],["THIAGO","M"],["ALINE","F"],["MARCIO","M"],["RENATA","F"],["ROBERTO","M"],["MARIANA","F"],["LUCAS","M"],["GUSTAVO","M"],["FELIPE","M"],["CAMILA","F"],["GUILHERME","M"],["SANDRA","F"],["CLAUDIO","M"],["LEANDRO","M"],["VANESSA","F"],["ANDREA","F"],["RENATO","M"],["FLAVIA","F"],["SIMONE","F"],["CARLA","F"],["RAIMUNDO","M"],["MARCO","M"],["DIEGO","M"],["PAULA","F"],["CRISTIANE","F"],["DANIELA","F"],["FLAVIO","M"],["GABRIEL","M"],["MANOEL","M"],["ANDERSON","M"],["CAROLINA","F"],["LUCIANO","M"],["RAQUEL","F"],["VINICIUS","M"],["AMANDA","F"],["EDSON","M"],["GABRIELA","F"],["JULIO","M"],["ROGERIO","M"],["MARIO","M"],["MONICA","F"],["BRUNA","F"],["MAURICIO","M"],["LETICIA","F"],["TIAGO","M"],["DEBORA","F"],["LARISSA","F"],["THAIS","F"],["PRISCILA","F"],["ALESSANDRA","F"],["DENISE","F"],["FABIANA","F"],["SILVIA","F"],["ADRIANO","M"],["VICTOR","M"],["TATIANA","F"],["MARINA","F"],["ELIANE","F"],["ANGELA","F"],["NATALIA","F"],["REGINA","F"],["JESSICA","F"],["RONALDO","M"],["SONIA","F"],["CAROLINE","F"],["VIVIANE","F"],["RITA","F"]],"plotOptions":{"displayType":"table","customPlotOptions":{},"pivotColumns":null,"pivotAggregation":null,"xColumns":null,"yColumns":null},"columnCustomDisplayInfos":{},"aggType":"","isJsonSchema":true,"removedWidgets":[],"aggSchema":[],"schema":[{"name":"PNOME","type":"\"string\"","metadata":"{}"},{"name":"SEXO","type":"\"string\"","metadata":"{}"}],"aggError":"","aggData":[],"addedWidgets":{},"dbfsResultPath":null,"type":"table","aggOverflow":false,"aggSeriesLimitReached":false,"arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .table-result-container {\n    max-height: 300px;\n    overflow: auto;\n  }\n  table, th, td {\n    border: 1px solid black;\n    border-collapse: collapse;\n  }\n  th, td {\n    padding: 5px;\n  }\n  th {\n    text-align: left;\n  }\n</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>PNOME</th><th>SEXO</th></tr></thead><tbody><tr><td>MARIA</td><td>F</td></tr><tr><td>JOSE</td><td>M</td></tr><tr><td>ANA</td><td>F</td></tr><tr><td>JOAO</td><td>M</td></tr><tr><td>CARLOS</td><td>M</td></tr><tr><td>PAULO</td><td>M</td></tr><tr><td>ANTONIO</td><td>M</td></tr><tr><td>LUIZ</td><td>M</td></tr><tr><td>FRANCISCO</td><td>M</td></tr><tr><td>MARCELO</td><td>M</td></tr><tr><td>MARCOS</td><td>M</td></tr><tr><td>ANDRE</td><td>M</td></tr><tr><td>RODRIGO</td><td>M</td></tr><tr><td>RAFAEL</td><td>M</td></tr><tr><td>RICARDO</td><td>M</td></tr><tr><td>FERNANDO</td><td>M</td></tr><tr><td>PEDRO</td><td>M</td></tr><tr><td>EDUARDO</td><td>M</td></tr><tr><td>FERNANDA</td><td>F</td></tr><tr><td>PATRICIA</td><td>F</td></tr><tr><td>DANIEL</td><td>M</td></tr><tr><td>ALEXANDRE</td><td>M</td></tr><tr><td>JULIANA</td><td>F</td></tr><tr><td>BRUNO</td><td>M</td></tr><tr><td>MARCIA</td><td>F</td></tr><tr><td>FABIO</td><td>M</td></tr><tr><td>LEONARDO</td><td>M</td></tr><tr><td>LUCIANA</td><td>F</td></tr><tr><td>SERGIO</td><td>M</td></tr><tr><td>LUIS</td><td>M</td></tr><tr><td>JORGE</td><td>M</td></tr><tr><td>CLAUDIA</td><td>F</td></tr><tr><td>ADRIANA</td><td>F</td></tr><tr><td>THIAGO</td><td>M</td></tr><tr><td>ALINE</td><td>F</td></tr><tr><td>MARCIO</td><td>M</td></tr><tr><td>RENATA</td><td>F</td></tr><tr><td>ROBERTO</td><td>M</td></tr><tr><td>MARIANA</td><td>F</td></tr><tr><td>LUCAS</td><td>M</td></tr><tr><td>GUSTAVO</td><td>M</td></tr><tr><td>FELIPE</td><td>M</td></tr><tr><td>CAMILA</td><td>F</td></tr><tr><td>GUILHERME</td><td>M</td></tr><tr><td>SANDRA</td><td>F</td></tr><tr><td>CLAUDIO</td><td>M</td></tr><tr><td>LEANDRO</td><td>M</td></tr><tr><td>VANESSA</td><td>F</td></tr><tr><td>ANDREA</td><td>F</td></tr><tr><td>RENATO</td><td>M</td></tr><tr><td>FLAVIA</td><td>F</td></tr><tr><td>SIMONE</td><td>F</td></tr><tr><td>CARLA</td><td>F</td></tr><tr><td>RAIMUNDO</td><td>M</td></tr><tr><td>MARCO</td><td>M</td></tr><tr><td>DIEGO</td><td>M</td></tr><tr><td>PAULA</td><td>F</td></tr><tr><td>CRISTIANE</td><td>F</td></tr><tr><td>DANIELA</td><td>F</td></tr><tr><td>FLAVIO</td><td>M</td></tr><tr><td>GABRIEL</td><td>M</td></tr><tr><td>MANOEL</td><td>M</td></tr><tr><td>ANDERSON</td><td>M</td></tr><tr><td>CAROLINA</td><td>F</td></tr><tr><td>LUCIANO</td><td>M</td></tr><tr><td>RAQUEL</td><td>F</td></tr><tr><td>VINICIUS</td><td>M</td></tr><tr><td>AMANDA</td><td>F</td></tr><tr><td>EDSON</td><td>M</td></tr><tr><td>GABRIELA</td><td>F</td></tr><tr><td>JULIO</td><td>M</td></tr><tr><td>ROGERIO</td><td>M</td></tr><tr><td>MARIO</td><td>M</td></tr><tr><td>MONICA</td><td>F</td></tr><tr><td>BRUNA</td><td>F</td></tr><tr><td>MAURICIO</td><td>M</td></tr><tr><td>LETICIA</td><td>F</td></tr><tr><td>TIAGO</td><td>M</td></tr><tr><td>DEBORA</td><td>F</td></tr><tr><td>LARISSA</td><td>F</td></tr><tr><td>THAIS</td><td>F</td></tr><tr><td>PRISCILA</td><td>F</td></tr><tr><td>ALESSANDRA</td><td>F</td></tr><tr><td>DENISE</td><td>F</td></tr><tr><td>FABIANA</td><td>F</td></tr><tr><td>SILVIA</td><td>F</td></tr><tr><td>ADRIANO</td><td>M</td></tr><tr><td>VICTOR</td><td>M</td></tr><tr><td>TATIANA</td><td>F</td></tr><tr><td>MARINA</td><td>F</td></tr><tr><td>ELIANE</td><td>F</td></tr><tr><td>ANGELA</td><td>F</td></tr><tr><td>NATALIA</td><td>F</td></tr><tr><td>REGINA</td><td>F</td></tr><tr><td>JESSICA</td><td>F</td></tr><tr><td>RONALDO</td><td>M</td></tr><tr><td>SONIA</td><td>F</td></tr><tr><td>CAROLINE</td><td>F</td></tr><tr><td>VIVIANE</td><td>F</td></tr><tr><td>RITA</td><td>F</td></tr></tbody></table></div>"]}}],"execution_count":0},{"cell_type":"code","source":["# With this registered as a temp view, it will only be available to this particular notebook. If you'd like other users to be able to query this table, you can also create a table from the DataFrame.\n# Once saved, this table will persist across cluster restarts as well as allow various users across different notebooks to query this data.\n# To do so, choose your table name and uncomment the bottom line.\n\npermanent_table_name = \"cadastro_nomes_sexo\"\n\ndf.write.format(\"parquet\").saveAsTable(permanent_table_name)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"db9631f6-bb4a-42ca-8a3c-0d48af932331"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\"></div>","removedWidgets":[],"addedWidgets":{},"type":"html","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}}],"execution_count":0},{"cell_type":"code","source":["df1 = table(\"cadastro_nomes_sexo\")\ndf2 = spark.sql(\"select * from cadastro_nomes_sexo\")"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"4f437e6a-b75f-4364-b948-21f3bcf003dc"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\"></div>","removedWidgets":[],"addedWidgets":{},"type":"html","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}}],"execution_count":0},{"cell_type":"code","source":["type(df1)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"f37178ed-75e1-4d50-bf48-7d7aa5ce8284"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\">Out[6]: pyspark.sql.dataframe.DataFrame</div>","removedWidgets":[],"addedWidgets":{},"type":"html","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\">Out[6]: pyspark.sql.dataframe.DataFrame</div>"]}}],"execution_count":0},{"cell_type":"code","source":["type(df2)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"8be1a7db-08b5-4dbc-a76a-3c443163fea5"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\">Out[7]: pyspark.sql.dataframe.DataFrame</div>","removedWidgets":[],"addedWidgets":{},"type":"html","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\">Out[7]: pyspark.sql.dataframe.DataFrame</div>"]}}],"execution_count":0},{"cell_type":"code","source":["df1.head(5)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"fae7172b-2d9e-4b75-b65c-16af138ca0ba"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\">Out[9]: [Row(PNOME=&#39;MARIA&#39;, SEXO=&#39;F&#39;),\n Row(PNOME=&#39;JOSE&#39;, SEXO=&#39;M&#39;),\n Row(PNOME=&#39;ANA&#39;, SEXO=&#39;F&#39;),\n Row(PNOME=&#39;JOAO&#39;, SEXO=&#39;M&#39;),\n Row(PNOME=&#39;CARLOS&#39;, SEXO=&#39;M&#39;)]</div>","removedWidgets":[],"addedWidgets":{},"type":"html","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\">Out[9]: [Row(PNOME=&#39;MARIA&#39;, SEXO=&#39;F&#39;),\n Row(PNOME=&#39;JOSE&#39;, SEXO=&#39;M&#39;),\n Row(PNOME=&#39;ANA&#39;, SEXO=&#39;F&#39;),\n Row(PNOME=&#39;JOAO&#39;, SEXO=&#39;M&#39;),\n Row(PNOME=&#39;CARLOS&#39;, SEXO=&#39;M&#39;)]</div>"]}}],"execution_count":0},{"cell_type":"code","source":["df2.take(5)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"7602f98d-d352-498c-8d2d-0c343f633b50"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\">Out[10]: [Row(PNOME=&#39;MARIA&#39;, SEXO=&#39;F&#39;),\n Row(PNOME=&#39;JOSE&#39;, SEXO=&#39;M&#39;),\n Row(PNOME=&#39;ANA&#39;, SEXO=&#39;F&#39;),\n Row(PNOME=&#39;JOAO&#39;, SEXO=&#39;M&#39;),\n Row(PNOME=&#39;CARLOS&#39;, SEXO=&#39;M&#39;)]</div>","removedWidgets":[],"addedWidgets":{},"type":"html","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\">Out[10]: [Row(PNOME=&#39;MARIA&#39;, SEXO=&#39;F&#39;),\n Row(PNOME=&#39;JOSE&#39;, SEXO=&#39;M&#39;),\n Row(PNOME=&#39;ANA&#39;, SEXO=&#39;F&#39;),\n Row(PNOME=&#39;JOAO&#39;, SEXO=&#39;M&#39;),\n Row(PNOME=&#39;CARLOS&#39;, SEXO=&#39;M&#39;)]</div>"]}}],"execution_count":0},{"cell_type":"code","source":["df1.show(5)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"cb99cbe1-93a2-4271-9b95-d2d469b5aa47"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\">+------+----+\n| PNOME|SEXO|\n+------+----+\n| MARIA|   F|\n|  JOSE|   M|\n|   ANA|   F|\n|  JOAO|   M|\n|CARLOS|   M|\n+------+----+\nonly showing top 5 rows\n\n</div>","removedWidgets":[],"addedWidgets":{},"type":"html","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\">+------+----+\n PNOME|SEXO|\n+------+----+\n MARIA|   F|\n  JOSE|   M|\n   ANA|   F|\n  JOAO|   M|\nCARLOS|   M|\n+------+----+\nonly showing top 5 rows\n\n</div>"]}}],"execution_count":0},{"cell_type":"code","source":["df2.count()"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"70dc5bd7-a753-40b4-8c84-a43d4b7e40d8"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\">Out[13]: 48970</div>","removedWidgets":[],"addedWidgets":{},"type":"html","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\">Out[13]: 48970</div>"]}}],"execution_count":0},{"cell_type":"code","source":[""],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"e77be729-e9cc-46df-8db9-741ea66ea85e"}},"outputs":[],"execution_count":0}],"metadata":{"application/vnd.databricks.v1+notebook":{"notebookName":"71-carga-dados-nome-sexo-pyspark","dashboards":[],"notebookMetadata":{"pythonIndentUnit":2},"language":"python","widgets":{},"notebookOrigID":2161532063358874}},"nbformat":4,"nbformat_minor":0}