Querying Data: Analytics

In this chapter

QueryIO supports on the fly metadata extraction from the files written onto the cluster. By default, the metadata for unregistered file types is saved in HDFS-METADATA table in the database. Query builder can be used to query the information extracted from the files.

• Query Manager
• Query Designer
• Query Viewer
• Spreadsheet Viewer

Query Manager

All the saved queries can be viewed under Query manager. You can load previously saved queries by clicking on respective query ids.

Various actions can be performed on Hadoop SQL Queries:

• Add
• To create a new Hadoop SQL query.
• Use Query Designer to design, save and execute new query.
• Design
• To redesign a Hadoop SQL query.
• Select a Hadoop SQL query and click "Design". This will take you to Query Designer with selected Hadoop SQL query details.
• View
• To view the result of selected Hadoop SQL query.
• Hadoop SQL query can be viewed on Query Viewer
• Delete
• To delete selected Hadoop SQL query.
• Clone
• To create exact copy of a Hadoop SQL query.
• Select query to be cloned.
• Specify cloned query id and description and click "Save".
• New exact copy of selected query will be created.
• Export
• Select query and click "Export".
• In the window that will appear, specify query id and select the format(PDF, HTML, XLS) in which you want the report to be exported.
• Click Export.
• Email
  • Select Hadoop SQL query and click "Email".
  • Specify query name, select the format(PDF, HTML, XLS) in which you want the report to be mailed and specify record names.
  • Select mail recipient from the list of registered users.
  • Click Email.
• Schedule
  • Execute Hadoop SQL query at scheduled time.
  • Select query and click "Schedule".
  • Specify schedule id, frequency interval, schedule time, select the format(PDF, HTML, XLS) in which you want the report of the Hadoop SQL query to be mailed.
  • Provide message to be included in email.
  • Select mail recipient from the list of registered users.
  • Click Email.

Query Designer

Query designer is composed of two modules:

• Query Builder
• Report Builder

Query Builder allows us to define grouping, sorting and constructing criteria in a simple and direct way. It also allows you to save queries and use them again later.

Query builder is basically used to filter out the files available on the cluster. Files can be filtered on the basis of file types(table name).

Using report builder interface of QueryIO designer, you can build reports to format the result of queries.

Query Builder

• To query extracted metadata, go to Analytics > Query Designer.

• Here you can see query builder view. Using this view, you can construct queries that you want to execute on your data.





• Click on SELECT textbox to select the names of the columns that you want to fetch with this query.

• Click on FROM textbox to select the name of the table on which you want to perform the query.

• Click on WHERE textbox to add any conditional clauses to your query. Only those records that fulfill the specified criteria will be included in the result.

• Click on GROUP BY textbox to select the names of the columns that you want to group your result set by. It is used in conjunction with the aggregate functions to group the result-set by one or more columns.

For example, the query "SELECT OWNER,SUM(LEN) FROM HDFS-METADATA GROUP BY OWNER" would return the total storage space utilized by each user.

• Click on ORDER BY textbox to select the names of the columns that you want to sort your result set by.

• Click on HAVING textbox to provide conditions on aggregate functions.

• Once you have constructed the query, click on Save button to save the generated query so that you can reuse it later.

• You can load previously saved queries by clicking on respective query IDs in the Queries view.

• Click on View to execute the query. On executing the query, you will be taken to Query Viewer.

Report Builder

Using report builder interface of QueryIO, you can build reports to present the contents of the queries that you can perform using the query builder interface.

Report builder comprises of three modules:

 

• Report Designer

  To specify the layout and design of various components of the report like report header, footer, etc.

• Chart Designer

  To specify the layout and design of the charts that you want to add to you report.

• Chart Preferences

  Chart Preference allows you to create highly customized charts.

• Report Preview

  In the report preview section, you can see the preview of the report that will be generated with the current configuration.

 

This tutorial will show you how easily you can build reports using QueryIO.

To build reports, go to Analytics > Query Designer.

Create new query using Query Builder view or load existing query using Query Manager page.

Following are the steps to build customized reports:

Report Designer

Report Header

The Report Header section generally contains the report title and other information you want to appear only at the beginning of the report. Charts in this section contain data for the entire report.

• To customize report header, click on the Edit button adjacent to the Report Header block in the report designer view.

• You can specify the contents of the report header in the Title edit box

• In this window, you can also configure the styles that would be applied to the report header.

• Click Done once you have configured all the properties as per your requirements.

Group Header

It contains the part of the message used to indicate the start of a functional group of documents. Group Headers are useful and often necessary to make your report data clear and easily understood.

• To customize group header, click on the Edit button adjacent to the Group Header block in the report designer view.

• To group your result set by various columns respective column names using adjacent checkboxes.

• You can also specify prefix and suffix for your group header value. Lets say, if the group header content is "admin" and you have selected prefix to be "User: ", then the group header content in the report will be
  User: admin

• Click Next to configure the styles that you want to apply to the selected group headers.

• Select the column name that you want to apply styles to and then edit other style related properties. You can configure styles for the other columns by selecting respective column names from the Column dropdown box.

• Click Next to specify custom formatting for group header content. Here you can specify the formatting of the data for various datatypes.

• Click Done once you have configured all the properties as per your requirements.

Column Header

The column header is the colored row of letters used to identify each column within the table. The column header row is located above the row one.

• To customize column header, click on the Edit button adjacent to the Column Header block in the report designer view.

• Select the column name that you want to apply styles to and then edit other style related properties. You can configure styles for the other columns by selecting respective column names from the Column dropdown box.

• Click Done once you have configured all the properties as per your requirements.

Column Details

Column details refer to the contents of the columns of non header rows in the table.

• To customize column details, click on the Edit button adjacent to the Column Details block in the report designer view.

  

• Select the column name that you want to apply styles to and then edit other style related properties. You can configure styles for the other columns by selecting respective column names from the Column dropdown box.

• Click Done once you have configured all the properties as per your requirements.

Group Footer

It contains the part of the message used to indicate the end of a functional group of documents. Group Footers are useful and often necessary to make your report data clear and easily understood.

• To customize group footer, click on the Edit button adjacent to the Group Footer block in the report designer view.

  

• If you want to group footers by various columns, you can do it here. Select respective column names using adjacent checkboxes.

• You can also select aggregate function to perform on the group footer content

  You can also specify prefix and suffix for your group footer value. Lets say, you have selected aggregate function to be SUM, prefix Total and suffix bytes, then the group footer content in the report will like
  Total 100 bytes where 100 is the sum of the values of the specified column in all the rows belonging to the current group.

• Click Next to configure the styles that you want to apply to the selected group footers.

• Select the column name that you want to apply styles to and then edit other style related properties. You can configure styles for the other columns by selecting respective column names from the Column dropdown box.

• Click Next to specify custom formatting for group footer content.

• Click Done once you have configured all the properties as per your requirements.

Report Footer

• To customize report footer, click on the Edit button adjacent to the Report Footer block in the report designer view.

• You can specify the contents of the report footer in the Title edit box

• In this window, you can also configure the styles that would be applied to the report footer.

• Click Done once you have configured all the properties as per your requirements.

Chart Designer

• Chart types supported are:

  • Line Chart
  • Bar Chart
  • Pie Chart
  • Area Chart
  • Bubble Chart
  • Scatter Chart
  • Meter Chart
  • Stock Chart
  • Difference Chart
  • Gantt Chart
  • Tube Chart
  • Cone Chart
  • Pyramid Chart
  • Radar Chart

Add Chart

• To add a chart, select Add action from the dropdown at the top of charts tab.
• Select the type of the chart that you want to design from the Chart Type dropdown box.
• Select the section of the report where you want to place your chart Section dropdown box. You can place your charts in report header, group header or group footer sections.
• You can create 2D and 3D charts by changing the Dimension property of the charts.
• If you want to add multiple charts to the same section, then you can specify custom layout for those charts by specifying custom values for the Row Position and Column Span properties.
• Click Add once you have configured all the chart related properties.

Clone Chart

• Use this action to create exact copy of the selected chart.

Delete Chart

• Use this action to delete selected chart.

Chart Preferences

QueryIO provides you highly customized your charts and make them the way you want them to look. This feature drastically improves overall report.

• Use Chart Preference action to define global chart settings which will be applied to all the charts added after chart preferences are saved.
• To define preference settings for individual charts, select the chart, and click Edit or click on the chart preview shown.

Chart Preference UI is divided into three sections :

• First section shows the configurable categories.
• Second section displays live preview of the chart. Any update in the properties are instantly reflected to the preview.
• Third section displays all the configurable properties that can be applied on the chart, according to selected category.

Chart Preferences include following configurable property categories

• Chart Area : This section deals with the overall area of the chart like background colors, outside border etc.
• Title : This category deals with chart title related properties.
• Leader Lines : These properties are applicable only for pie charts. This section contains properties related to leader lines of the pie chart.
• X-Axis : Here you can update properties related to X-Axis labels like position above or below, colors, borders etc.
• Y-Axis : Here you can update properties related to Y-Axis labels like position left or right, colors, borders etc.
• Labels : This properties are related to data labels that shows the exact value of the quantity in chart.
• Legend : This section deals with the properties related to legend table like table position : left or right,table anchor: top, middle or bottom, borders, colors etc.

Note : For global chart preferences, all categories are shown and preview contains pie chart and line chart. For individual chart preferences, categories not applicable on respective chart are not displayed and preview contains selected chart type.

Report Preview

Here you can see the preview of the report that will be generated with the current configuration.

Query Viewer

Query Viewer is used to display the result of the query. All View query requests from Query manager and designer are displayed in Query viewer. Hadoop SQL queries can be executed through viewer also.

Print / Export / Email Report

Click on View to view generated report. Once the report is generated, it can be printed, exported to local system and can be mailed to registered users of QueryIO.

• Print
• Click Print to print currently opened report.
• Export
• Click on "Export" button.
• In the window that will appear, specify query id and select the format(PDF, HTML, XLS) in which you want the report to be exported.
• Click Export.
• Email
• Click on "Email" button.
• Specify query name, select the format(PDF, HTML, XLS) in which you want the report to be mailed and specify record names.
• Select mail recipient from the list of registered users.
• Click Email.

SpeadSheet Viewer

QueryIO provides support for spreadsheet viewer to process and further analyze the query results. Spreadsheet viewer provides a excel like spreadsheet with all the layers of business intelligence.

Result of all the queries executed can be viewed in SpreadSheet Viewer for further processing.

Spreadsheet viewer allows sheet creation, viewing, and editing processes. It also supports Undo & redo, Merge-able cells, Easy creation of custom formula functions and variables, full screen mode for better viewing, printing reports and many more features.

Copyright ©

Contact Us

Contact Us

2018 QueryIO Corporation. All Rights Reserved.

QueryIO, "Big Data Intelligence" and the QueryIO Logo are trademarks of QueryIO Corporation. Apache, Hadoop and HDFS are trademarks of The Apache Software Foundation.