And probably more to come if I’m not missing some of them already.<\/p>\n
Why are we starting on the Query Environment? Because the Query Environment has some similar features to the Data Wrangler. Let’s discover them first and compare with the features on the Data Wrangler.<\/p>\n
This example starts on a SQL Endpoint of a lake house.<\/p>\n
- \n
- Create a Visual Query<\/strong>.<\/li>\n<\/ol>\n
![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/a-close-up-of-words-description-automatically-gen.png\")
<\/p>\n<\/p>\n
- \n
- Drop the table Fact_Sales<\/em> to the Visual Query<\/strong><\/li>\n<\/ol>\n
![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/a-screenshot-of-a-computer-description-automatica.png\")
<\/p>\n- \n
- On the top menu, on the Settings<\/strong> button, the Data View<\/strong> item has some interesting features for us to investigate. Let’s analyze them one by one. Description automatically generated”<\/li>\n<\/ol>\n
<\/p>\n
![\""A\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/a-screenshot-of-a-computer-description-automatica-1.png\")
<\/p>\nEnable Column Profile<\/h2>\n
When we enable this option, a subtle green line appears on the Display Area, just below the title. If we hover this green line on a specific column, we find information about the data quality of the rows on that column.<\/p>\n
The information tells us how many rows have valid values, error values and empty values for the column.<\/p>\n
![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/a-screenshot-of-a-computer-program-description-au.png\")
<\/p>\n<\/p>\n
Show Column Quality Details<\/h2>\n
Enabling this option expands the green line. The information about data quality which was only visible when hovering a column becomes visible in the expanded panel.<\/p>\n
![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/a-screenshot-of-a-computer-description-automatica-2.png\")
<\/p>\n<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/word-image-97681-6.png\")
<\/p>\n<\/p>\n
Show Value Distribution<\/h2>\n
This option adds to the panel information about the distribution of unique values in the column. It points to us how many distinct and unique values each column has.<\/p>\n
![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/a-screenshot-of-a-computer-description-automatica-3.png\")
<\/p>\n<\/p>\n
This information may be very useful to identify primary keys in sets of data you don’t know.<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/word-image-97681-8.png\")
<\/p>\n<\/p>\n
Data Wrangler<\/h2>\n
The Data Wrangler has similar information to the ones we just found on the Query Environment. However, while the query environment works in a visual environment, the data wrangler works on python notebooks.<\/p>\n
Two additional details it worth mentioning: The Data Wrangler has more features, linked to spark notebooks and it’s difficult to locate if you don’t know exactly where to look.<\/p>\n
Opening the Data Wrangler<\/h2>\n
The secret to opening the data wrangler is simple after you discover it: The Data Wrangler requires a Pandas<\/strong> data frame. Once we have a notebook opened and a Pandas<\/strong> dataframe loaded into a variable, the Data Wrangler<\/strong> becomes available.<\/p>\n
As an example, we will use the same lake house as the previous example.<\/p>\n
Let’s follow these steps:<\/p>\n
- \n
- Click the Experience<\/strong> button and select the Data Engineering<\/strong> experience.<\/li>\n<\/ol>\n
![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/a-screen-shot-of-a-computer-description-automatic.png\")
<\/p>\n<\/p>\n
- \n
- Click the button New Notebook<\/strong> on the Data Engineering<\/strong> experience.<\/li>\n<\/ol>\n
![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/a-screenshot-of-a-computer-description-automatica-4.png\")
<\/p>\n<\/p>\n
- \n
- On the top left, change the notebook name to WranglerTest<\/em><\/li>\n<\/ol>\n
![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/a-screenshot-of-a-computer-description-automatica-5.png\")
<\/p>\n<\/p>\n
- \n
- On the left, click the Add<\/strong> button to link the notebook with the lake house.<\/li>\n<\/ol>\n
![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/a-screenshot-of-a-computer-description-automatica-6.png\")
<\/p>\n<\/p>\n
<\/p>\n
- \n
- On the Add lakehouse window, select the Existing lakehouse option.<\/li>\n<\/ol>\n
![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/a-screenshot-of-a-computer-description-automatica-7.png\")
<\/p>\n<\/p>\n
<\/p>\n
- \n
- Choose your lakehouse and click the Add button.<\/li>\n<\/ol>\n
![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/a-screenshot-of-a-computer-description-automatica-8.png\")
<\/p>\n<\/p>\n
<\/p>\n
- \n
- Drag and drop the dimension_customer<\/em> table to the code block in the notebook.<\/li>\n<\/ol>\n
The code to load the table is automatically created. Did you know this one?<\/p>\n
![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/a-screenshot-of-a-computer-description-automatica-9.png\")
<\/p>\n<\/p>\n
<\/p>\n
![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/a-screenshot-of-a-computer-program-description-au-1.png\")
<\/p>\n<\/p>\n
- \n
- Remove the Display<\/strong> line in the code. It’s not needed.<\/li>\n
- Add toPandas() <\/strong>at the end of Spark.sql<\/strong><\/li>\n<\/ol>\n
![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/a-screenshot-of-a-computer-description-automatica-10.png\")
<\/p>\n<\/p>\n
<\/p>\n
- \n
- Execute the code block.<\/li>\n<\/ol>\n
We will receive an error message and it explains the problem. Pandas, in the lake house environment, always try to make an optimization called Arrow optimization by default. This optimization may not work well with some fields, so we need to disable it.<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/word-image-97681-18.png\")
<\/p>\n<\/p>\n
- \n
- Add the following line to the start of the code:<\/li>\n<\/ul>\n
<\/p>\n
\nspark.<\/span>conf.<\/span>set('spark.sql.execution.arrow.pyspark.enabled'<\/span>, 'false'<\/span>)\r\n<\/pre>\n<\/div>\n- \n
- Run the code block again.<\/li>\n<\/ol>\n
The Arrow optimization is disabled but the datetime field still causes problems. To make it easier, let’s remove the datetime field from the query.<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/word-image-97681-19.png\")
<\/p>\n<\/p>\n
- \n
- Change the SQL query in the code to the following:<\/li>\n<\/ol>\nSELECT<\/span>\u00a0customerkey<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0wwicustomerid<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0billtocustomer<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0category<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0buyinggroup<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0primarycontact<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0postalcode<\/span>
\nFROM<\/span>\u00a0\u00a0\u00a0demolake<\/span>.<\/span>dimension_customer<\/span>
\nLIMIT<\/span>\u00a0\u00a01000<\/span>\u00a0 <\/span><\/div>\n- \n
- Execute the code block again.<\/li>\n<\/ol>\n
![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/a-white-card-with-black-text-description-automati.png\")
<\/p>\n<\/p>\n
- \n
- On the top menu, click Data<\/strong> menu item.<\/li>\n
- Click the Launch Data Wrangler<\/strong> button.<\/li>\n<\/ol>\n
The variable created on the code block is identified and appears on the menu.<\/p>\n
![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/a-screenshot-of-a-computer-description-automatica-11.png\")
<\/p>\n<\/p>\n
- \n
- Click the variable name on the menu. The Data Wrangler<\/strong> will appear.<\/li>\n<\/ol>\n
![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/a-screenshot-of-a-computer-description-automatica-12.png\")
<\/p>\n<\/p>\n
Data Wrangler: First Impressions<\/h2>\n
The initial Data Wrangler window seems like the information we have in the query environment. It seems to have some additional details about the data distribution, in some cases, making it more visible when there are only a few unique values.<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/word-image-97681-23.png\")
<\/p>\n<\/p>\n
If your purpose is only to see this additional information about the columns, both Data Wrangler and the query environment, work. It becomes a matter of preference which one you should use and for sure you will choose according to your preferred environment: If you prefer to work visually, the query environment will be better, if you prefer to work with spark, Data Wrangler<\/strong> will be better.<\/p>\n
However, Data Wrangler can achieve much more. It can accept transformations over the data frame and implement these transformations as pyspark<\/strong> code.<\/p>\n
In fact, the UI is slightly like Power Query<\/strong>.<\/p>\n
<\/p>\n
Transformations with Data Wrangler<\/h2>\n
Let’s implement some transformations over the data frame and check the result.<\/p>\n
- \n
- Click the Expand<\/strong> button (“…”<\/em>) beside the BillToCustomer<\/em> field.<\/li>\n<\/ol>\n
![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/a-screenshot-of-a-computer-description-automatica-13.png\")
<\/p>\n<\/p>\n
- \n
- Select the Filter<\/strong> option on the menu.<\/li>\n
- On the left side, on the dropdown Select a Condition<\/strong>, select the option Starts With<\/strong><\/li>\n<\/ol>\n
![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/a-screenshot-of-a-computer-screen-description-aut.png\")
<\/p>\n<\/p>\n
- \n
- On the 3rd textbox, type WingTip<\/em><\/li>\n<\/ol>\n
![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/a-screenshot-of-a-computer-description-automatica-14.png\")
<\/p>\n<\/p>\n
- \n
- Click the Apply<\/strong> button.<\/li>\n<\/ol>\n
Mind the Cleaning steps<\/strong>, registering the transformations.<\/p>\n
![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/a-screenshot-of-a-computer-description-automatica-15.png\")
<\/p>\n<\/p>\n
- \n
- Click the Expand<\/strong> button (“…”<\/em>) beside the PostalCode<\/em> field.<\/li>\n
- Select the Drop Columns<\/strong> option on the menu.<\/li>\n<\/ol>\n
![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/a-screenshot-of-a-computer-description-automatica-16.png\")
<\/p>\n<\/p>\n
- \n
- Click the Apply<\/strong> button.<\/li>\n<\/ol>\n
![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/a-screenshot-of-a-computer-description-automatica-17.png\")
<\/p>\n- \n
- On the top menu, click the Add <\/strong>code to notebook button.<\/li>\n<\/ol>\n
![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/a-screenshot-of-a-computer-description-automatica-18.png\")
<\/p>\n<\/p>\n
The transformations created visually are included in the notebook as part of the code.<\/p>\n
![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/a-screenshot-of-a-computer-code-description-autom.png\")
<\/p>\n<\/p>\n
Summary<\/h2>\n
The Data Wrangler<\/strong> is a powerful tool not only to help data exploration but also to help building pyspark<\/strong> code using visual methods.<\/p>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
The Data Wrangler is as interesting as hidden inside Microsoft Fabric. It’s not easy to find and activate it, but it is worth the trouble. Before digging into the Data Wrangler, let’s analyze the data features in the query environment. Data Features in Query Environment The new query environment, which allow us to make data……<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2,159164],"tags":[159016,123646,159015,158998,158997],"coauthors":[6810],"class_list":["post-97681","post","type-post","status-publish","format-standard","hentry","category-blogs","category-microsoft-fabric","tag-data-engineering","tag-data-lake","tag-data-wrangler","tag-lakehouse","tag-microsoft-fabric"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/97681","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=97681"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/97681\/revisions"}],"predecessor-version":[{"id":99098,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/97681\/revisions\/99098"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=97681"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=97681"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=97681"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=97681"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
- On the top menu, click the Add <\/strong>code to notebook button.<\/li>\n<\/ol>\n
- Select the Drop Columns<\/strong> option on the menu.<\/li>\n<\/ol>\n
- Click the Expand<\/strong> button (“…”<\/em>) beside the PostalCode<\/em> field.<\/li>\n
- Click the Apply<\/strong> button.<\/li>\n<\/ol>\n
- On the left side, on the dropdown Select a Condition<\/strong>, select the option Starts With<\/strong><\/li>\n<\/ol>\n
- Select the Filter<\/strong> option on the menu.<\/li>\n
- Click the Expand<\/strong> button (“…”<\/em>) beside the BillToCustomer<\/em> field.<\/li>\n<\/ol>\n
- Click the Launch Data Wrangler<\/strong> button.<\/li>\n<\/ol>\n
- On the top menu, click Data<\/strong> menu item.<\/li>\n
- Execute the code block again.<\/li>\n<\/ol>\n
- Change the SQL query in the code to the following:<\/li>\n<\/ol>\n
- Run the code block again.<\/li>\n<\/ol>\n
- Add the following line to the start of the code:<\/li>\n<\/ul>\n
- Add toPandas() <\/strong>at the end of Spark.sql<\/strong><\/li>\n<\/ol>\n
- Remove the Display<\/strong> line in the code. It’s not needed.<\/li>\n
- Drag and drop the dimension_customer<\/em> table to the code block in the notebook.<\/li>\n<\/ol>\n
- Choose your lakehouse and click the Add button.<\/li>\n<\/ol>\n
- On the Add lakehouse window, select the Existing lakehouse option.<\/li>\n<\/ol>\n
- On the left, click the Add<\/strong> button to link the notebook with the lake house.<\/li>\n<\/ol>\n
- On the top left, change the notebook name to WranglerTest<\/em><\/li>\n<\/ol>\n
- Click the button New Notebook<\/strong> on the Data Engineering<\/strong> experience.<\/li>\n<\/ol>\n
- Click the Experience<\/strong> button and select the Data Engineering<\/strong> experience.<\/li>\n<\/ol>\n
- On the top menu, on the Settings<\/strong> button, the Data View<\/strong> item has some interesting features for us to investigate. Let’s analyze them one by one. Description automatically generated”<\/li>\n<\/ol>\n
- Drop the table Fact_Sales<\/em> to the Visual Query<\/strong><\/li>\n<\/ol>\n