Kognitio Console Training June 2014

1. Kognitio Console TrainingJune2014 • June 2014

2. Using this slide deck • All title slides are hyperlinked for easy navigation simply click on the relevant section/topic you are interested in or view the deck in order for the full course • There are no exercises in this training, the deck has been designed for you to walk through Kognitio Console as you read the deck. Instructions are in the green boxes and learning is in the blue boxes • Every slide has two return buttons. One to the Main menu (slide 3) and one to the relevant section menu • Any comments or questions should be directed to the Kognitio member of staff that supplied this slide deck. Feedback is welcome as it is essential for us to improve training and ensure we cover all relevant topics.

3. Contents • Quick start guide to Kognitio Console • Features • Colours and Icons • Object tabs • Configuration • Reports • QuickViz • Import and export, load and unload • SQL Scripting • Kog Scripting • Administration

4. Quick start: contents • Connecting • Schemas and tables • SQL querying • Users • Sessions

5. Quick start: connecting – logging on Connections can be made to any Kognitio DSN already set up via the ODBC Administrator tool Log on to Kognitio system using an existing user. This can be the SYS user if this is a newly commissioned system

6. Quick start: connecting – entry screen Update explorer tree (as required) This is the standard Kognitio Console entry screen. The quick start will look at schemas, submitting basic SQL and introduce users and sessions Note: The exact contents of the explorer tree will depend on the user’s privileges

7. Quick start: table – double click More details about the different object view tabs are covered in object tabs in the features section Expand out the schema tree to show an existing schema and associated tables and views Double click on any table or view to see the object info in the object pane view

8. Quick start: table – right click Right click on any table to create or drop table image from RAM, including a fragmented image, drop the table, update statistics and load and unload from the table More details about loading, unloading and the legacy import and export tools are covered in the features section

9. Quick start: view – right click Right click on any view to create or drop view image from RAM, drop the view, update statistics or Unload/export the view More details about exporting and unloading are covered in the features section

10. Quick start: SQL – drag and drop Click on SQL button Click on SQL View Drag and drop any view or table into SQL editor… This opens a standard SQL query pane that allows one query to be written and submitted each time When a table is dragged onto an empty line a “select * from query” is automatically created

11. Quick start: SQL – execution Click on execute button to run query On execution results and metadata for results are generated…. …and SQL details including timings shown in query history

12. Quick start: SQL – drag and drop column When you amend a query in the standard SQL view the tab name increments by 1 Queries can also be run from the right-click menu Drag and drop any column into Query tab… When a column is dragged and dropped only its name appears. This can be used in SQL queries

13. Quick start: SQL – configuration Click on configure button or use the tools/configure menu to open the options dialog

14. Quick start: SQL - query navigation Two ways to navigate: Click the back and forwards arrows Double click on query in the query history pulls up the tab for that query

15. Quick start: SQL – copying results You can highlight cells to copy The right-click menu is used to resize and copy results Clicking “Copy Selected” or “Copy All” means the header is copied too!

16. Quick start: SQL – query error Error is highlighted Any errors in SQL execution are shown in the Error tab In query history errors are highlighted with a red cross

17. Quick start: results – query correction When the error is corrected and run another tab is created and incremented

18. Quick start: users – schema privileges Double clicking on any user brings up their user information in the object view pane Single clicking on an object shows the direct privileges of the user on the object, and those inherited from its groups First expand the user tree and double click on the user currently logged in… … and then single click on a schema (or any object)

19. Quick start: adding privileges Privileges that are not relevant are greyed out. Select object to grant privileges on Expand the privileges tree Drag and drop required privileges from privileges tree All privileges are grouped under the objects they apply to. Save changes

20. Quick start: privileges To remove a privilege highlight it and hit the delete key To make a privilege grantable highlight it and type + (plus) To remove the grantable privilege type – (minus) Save changes

21. Quick start: sessions overview The object view shows you the sessions of users you have privileges to view Double click on sessions Click on a session to see last query executed Right click on a session in the object tree to see option to abort query or session There are also buttons to abort query or session Take care not to abort the sessions associated with this instance of Kognitio Console!!!!

22. Features: contents • Colours and Icons • Object Tabs • Configuration • Reports • QuickViz • Import and export • SQL Scripts • Kog Scripts

23. Features: colours and icons for table and views Icons show types Dark blue means horizontally fragmented Blue means in memory Dark blue means vertically fragmented

24. Features: object tabs – info Info Tab shows schema, table, type, id, owner, create date and row count (if it can be determined at low cost). Double click on a table to show the Info tab in the object view pane

25. Features: object tabs – columns Click on Columns tab to show the column information Column tab shows the name, type, display size, scale, precision, nullability and comments of each column in the table

26. Features: object tabs – table editing Double-click on table cell to edit (only compatible changes allowed)

27. Features: object tabs – content Click on Content tab to get a sample of table contents If there is NO image in RAM this can take some time as Kognitio has to run the SQL to get the sample

28. Features: object tabs – keys Click on Keys tab to see the table’s keys

29. Features: object tabs – privileges Click on Privileges tab to show the privileges associated with the selected table Privilege tab shows the user name, privilege, grantability and the specific columns in the table to which the privilege applies

30. Features: object tabs – dependencies Click on Dependencies tab to show the views that depend on the selected table The tree structure here is fully expandable to show all dependencies. Views may appear many times if dependent on multiple views Double clicking on a dependency will show that table or view in the Object View

31. Features: object tabs – create text Click on Create Text tab to see SQL used to create table For tables and views with images the create image text will also be shown

32. Features: object tabs – disk distribution Click RAM / Disk tab for access to further tabs Disk Distribution shows the data distribution across mpids and slabs The Disk Distribution tab is not shown for RAM only tables, or for some small tables.

33. Features: object tabs – slab allocation Click on the Slab Administration tab to check slab allocation or re-assign slabs if you have the privilege Slab Administration shows all slabs in the system and those to which the table is currently assigned

34. Features: object tabs – RAM usage Details about table and row headers, null masks, alignment bytes and deleted data are displayed. Uncompressed size, with header, alignment, etc. overhead Click on the RAM Usage tab to check how much RAM is being used by the table Compressed size, with overhead from compression Compressed images are a new feature in Kognitio Analytics Platform version 8.0

35. Features: object tabs – alignment Click on the Alignment tab for information on table alignment Alignment gives information on column sequence, type, location, length and alignment to investigate disk space utilisation

36. Features: object tabs – optimal alignment Click on the Optimization tab to obtain a create table statement with the columns arranged in an order to reduce RAM usage The optimised create table statement minimises the number of alignment bytes required

37. Features: object tabs – Partitions Click on the Partitions tab to show partitioned image information Partitioned images are a new feature in Kognitio Analytics Platform version 8.0 Partitions work best when there are lots of records per partition and the RAM used is similar for each partition.

38. Features: object tabs – CDMs Click on the Compressed Data Maps tab to get information on compressed data maps Compressed Data Maps allow faster access to data on disk.

39. Features: object tabs – Relationships Member of unique group Primary key Toolbar for entity diagram Thumbnail view Not NULL Click on the Relationships tab to get entity diagram Nullable References and uses relationships for the table

40. Features: object tabs – Relationships Object View for IPE_ALLUSER References relationship View Uses relationship Table Blue means Column in Memory

41. Features: object tabs – Relationships Click here to expand/contract the table Same icon Table Select “Insert Text” from menu, then click in diagram to insert text T Border colour shows image type View If items are moved that position is restored the next time the diagram is shown Table comment

42. Features: creating external scripts and tables Creating External Scripts and External Tables

43. Features: object tabs – External tables Click on the External Table tab to get information on the external table definition External Tables have a red icon with an “x” External tables are a new feature in Kognitio Analytics Platform version 8.0

44. Features: object tabs – Script Environments Before External Scripts may be used a Script Environment must be defined Script Environments are shown in their own section Script Environments are a new feature in Kognitio Analytics Platform version 8.0

45. Features: object tabs – External Scripts External Scripts are shown in the schema to which they belong Script Environments are a new feature in Kognitio Analytics Platform version 8.0

46. Features: object tabs – External Scripts External Scripts are a new feature in Kognitio Analytics Platform version 8.0

47. Features: object tabs – External Data Sources Double-click to view contents Click to bring up browser Popup menu option to create external table (Hadoop and Kognitio connectors only) External Data Source browsing is a new feature in Kognitio Analytics Platform version 8.0

48. Features: object tabs – External Data Sources For Hadoop file contents profiled to guess column definitions Change for data format Preview of table, using column definitions Column definitions may be edited

49. Features: Create External Data Source Wizard Choose type of external data source Give it a name Popup menu Alter UI parameters, usually defaults are sufficient SQL to create external data source Click to execute SQL

50. Features: object tabs – User privileges Shows privileges a User has on all objects, including inherited privileges from the groups a use belongs to.