Use Netezza with Apache Superset for data visualization and analysis

In this article, you will learn how to use Apache Superset (a modern, enterprise-ready business intelligence web application) with Netezza database for unravelling the story behind the data.

Data analysis is one of the most important processes that businesses can leverage to explore data in meaningful ways for faster, profitable, and effective decision-making. Toward that end, tools that offer fast and intuitive interfaces with analytics capabilities are invaluable. One such example is Apache Superset -- is a modern platform for data visualization and analysis. It offers a wide range of interactive charts, tables, maps, and graphs, and is easy to integrate with a modern database like Netezza. To set up Apache Superset, checkout the Apache.org docs.

Hate crime dataset

This data comes from the Kaggle repository and represents the FBI annual hate crime report from 2020, containing information about 219,000 cases since 1991. It features columns with ID, date, place, type, and bias of an incident, as well as information about victim and offender. Dealing with this type of data, we must be very careful before inadvertently drawing any conclusions. Nevertheless, it is important to be able to get insights from past reports to help understand and prevent occurrences of similar incidents in the future.

In the next parts of this article, we will show how Apache Superset integrated with Netezza makes it easy for you to visualize and analyze the data.

Connecting superset to database

Once your Apache Superset platform is set up, you need to ensure that your data is properly loaded in a database. First, create a table with proper columns.

create table hate_crime (INCIDENT_ID bigint, DATA_YEAR varchar(10) , ORI varchar(20), PUB_AGENCY_NAME varchar(100),  PUB_AGENCY_UNIT varchar(100), AGENCY_TYPE_NAME varchar(50), STATE_ABBR varchar(3), STATE_NAME varchar(50), DIVISION_NAME varchar(50), REGION_NAME varchar(50), POPULATION_GROUP_CODE varchar(3), POPULATION_GROUP_DESC varchar(100), INCIDENT_DATE date, ADULT_VICTIM_COUNT bigint, JUVENILE_VICTIM_COUNT bigint, TOTAL_OFFENDER_COUNT bigint, ADULT_OFFENDER_COUNT bigint, JUVENILE_OFFENDER_COUNT bigint, OFFENDER_RACE varchar(100), OFFENDER_ETHNICITY varchar(100), VICTIM_COUNT bigint, OFFENSE_NAME varchar(200), TOTAL_INDIVIDUAL_VICTIMS bigint, LOCATION_NAME varchar(100), BIAS_DESC varchar(200), VICTIM_TYPES varchar(200), MULTIPLE_OFFENSE varchar(2), MULTIPLE_BIAS varchar(2));

Then you can use the nzload command to insert the CSV file inside the table:

nzload -df hate.csv -db visual -t hate_crime -u admin -pw password -nullValue NA  -boolStyle Yes_No -skipRows 1 -delim ',' -dateStyle DMONY2 -dateDelim '-' -y2base 1991

The data is ready to perform operations on, but we need to create the connection to it from Apache Superset. From the databases panel, click +Database and a window should open. The display name is arbitrary, but it should be unique in your databases list. The SQLAlchemy URL is constructed in such a manner:

dialect+driver://username:password@host:port/database

In the case of Netezza, it would look as follows, with VISUAL being a name of database in Netezza:

netezza+nzpy://username:password@host:port/database

You can test whether the URL is spelled correctly by clicking Test connection in case of any issues like wrong password or inaccurate privileges. If everything is OK, you can personalize options in the Advanced tab. When it’s all set, you need to create the dataset on which you will work. Go to the datasets panel and click on +Dataset and choose the proper database, schema, and table. In our case:

Database: Visualization Schema: ADMIN Table: HATE_CRIME

And that is it. Now you are ready to create tables. You can also get more information about connecting Apache Superset to a database.

The story behind the data

So, let’s dig into the data. You need to click on +Chart in the Charts tab and choose your dataset. Before proceeding, you need to decide what info you want to extract. If you know the answer to that, you can proceed to choosing the chart that is most appropriate for visualizing that information. This decision is important as it could help your viewers engage more and remember more of what you’re trying to tell them. For example, a simple table might be suitable for various kinds of statistics, but charts like sunburst, histogram, or graph might make it easier for audience to assimilate the knowledge behind them.

Apache Superset has this amazing capability of creating a map of a given country with regard to the given administrative unit. In case of the United States, it’s divided into the states. We can see that areas with the most reported occurrences of hate crimes are California, New York, and new Jersey. On the opposite side are states like Wyoming and Montana. Obviously, this distribution is highly dependent on the size of population, but it does not fully explain the situation. For example, Texas and Florida who are second and third in population still had fewer incidents than Michigan.

Maps aren’t the only tool to analyze geographical dependencies. We might combine the knowledge of regional distribution of incidents with info about most common biases. Bias tells us which prejudice caused the offense. As shown below, almost everywhere the highest number of incidents is assigned to Anti-Black or African American, but mid-Atlantic has visible overrepresentation of Anti-Jewish cases related to other divisions.

If the dataset contains at least one time data column, you might use it to create time-based visualizations. In the image below, there is an area chart showing what percentage of all incidents a given region is responsible for over time. You can observe that in the past, the Northeast was the leader with over 50 percent of all hate crimes, but over time, it evened out among four main regions with the West on top for the largest part of recorded history.

Speaking of time distribution, you might wonder whether the number of incidents is growing, reducing, or if it is relatively stable. The answer to that question is presented in the following image. Trends are not explicit, but a closer look could tell you that the numbers were increasing there in the beginning of the 1990s and slowly decreasing up to around 2015, when they start growing again. What we can observe clearly is the influence of two events that temporarily spiked hate-crime occurrences: the Sept. 11 attacks and the death of George Floyd.

We've analyzed regional and time distribution of incidents, but still don’t know much about the character of these crimes. For example, we can look at the biases. In the previous image, you can see graphical representation of the most common offense types (represented by blue circles) and locations (represented by circles in distinct colors). The size of a circle represents the frequency of this particular location/offense type in the dataset, and the thickness of an arrow indicates the occurrence frequency of those two in the same case. For example, if you look at the Highway/Road/Alley/Street/Sidewalk circle, you could see that it is more common to experience Simple Assault there than Intimidation, although intimidations would seem more common generally.

To get information about other whereabouts of crimes, you may look at the next image, showing a word cloud of locations these incidents took place in. We can see that common locations are Church/Synagogue/Temple/Mosque or Parking/Drop Lot/Garage.

Naturally, that is not all that we can extract from this dataset because there are plenty of other charts available in Apache Superset. Even presented ones could be altered by using different filters or changing metrics.

You might wonder how to organize all these charts, especially if you plan to do the analytics on more than one database. Apache Superset comes with solution in the form of dashboards. It’s a tool that keeps all charts related to one story in a single place. It’s also easily organizable so you can personalize the view for your own needs. Below, you can see all previously mentioned charts arranged in way that is easy to read and interpret. You can also add some text boxes to be more descriptive about the data.

Now we'll discuss how to create a chart in Apache Superset. You’ll see how fast and intuitive it is. For example, to create a line chart, you need to have a time column; if there is only one of this type in the dataset, it should be selected automatically. You can choose the time grain of the visualization. The smaller the time grain, the longer it may take to create a chart. Next, we have to decide on the metric; in our case, it will be count(*) because we want to count the occurrences of incidents.

And that’s it! There is nothing else you have to do. If you click Run, the chart should appear within a few seconds. On the screen, you should see the result that is similar to picture from the Time distribution section. To distinguish different years with colors, there is one more box that needs to be filled: group by, where we need to select the year column. You can also alter the look of the chart by clicking the customize tab and changing some parameters.

To create a map chart as shown above, you need to first select the country. In this case, it’s the United States. For the next step, you need to have a column with state codes according to the ISO 3166-2 standard (Colorado has to be represented by US-CO and Virginia by US-VA). The hate crime dataset originally had only second part of this code, so the state_abbr column must be altered with a SQL command. If the correct column is present, you should choose it in the ISO 3166-2 Codes box. Here, as in the previous example, the metric has to be selected and we will go with count(*) again.

You can always change some parameters. For example, you might filter out only specific crimes or biases to see how it changes the view. To customize the view, you can change the color scheme and format of the number that appears after you mouse over some state. There is also an option to change a metric to analyze different aspects of crimes. If you click on Metrics and go to a tab named Simple, you may choose offense_name as a column and count_distinct as an aggregate. Then, if you click Run, the map will change completely, with Washington and Michigan with the highest score.

Summary

To sum up, if you’re looking for modern, efficient, and easy-to-use tool to perform analytics on your data, Apache Superset is a smart choice. In this article, we have shown how set it up a connection to your Netezza database and create informative charts without much effort. We’ve also presented an example of how you might get into the story that is hidden inside raw table data.