Tips for Installing Jethro

1. Jethro process queries by first evaluating the WHERE clause and determining the rows needed for the query. It then fetched column data only for those rows. The narrower the query, the faster it performs

1. Use numeric formats (INT/BIGINT, FLOAT/DOUBLE) whenever possible - any string column that holds only numeric values should be converted. This is especially true for high cardinality columns.
2. Use TIMESTAMP for Date/Time columns. Jethro creates multiple date-related indexes for such columns to improve performance of date-range queries.

1. A TIMESTAMP column is typically best choice for partitions as it simplifies maintenance tasks like purging old data
2. Jethro recommends a total of 5-25 partitions although it comfortably supports hundreds of partitions
3. Jethro partition key can use range values. For example: PARTITION BY RANGE(ts_col) EVERY (INTERVAL ‘7’ DAY)  

1. Jethro uses server-side caching for metadata and frequently used file fragments. The greater the space the more data it will be able to store. Note that the benefit of the cache will be realized over time as filling up the cache can take some time.
2. Cache space can be defined when an instance is created or updated later on by editing the local-conf.ini file.
3. Jethro automatically enables query-result cache. The query-result cache is stored in HDFS and does not require local disk space.

1.  While Jethro optimizes JOINs and automatically performs Star-Transformation, it is better to avoid them when not required.
2. Jethro's columnar format and effective compression minimize the storage impact of such denormalization.

1. More CPU and RAM Improves query speed as Jethro takes advantage of multi-threading. It also improves concurrency as more user/queries can be served in parallel.
2. 10Gb network connectivity to cluster will speed up HDFS access.
3. Local drives for caching – SSD is preferable.
4. Trial servers can start with as little as 64GB and 8 cores.

1. Multiple servers linearly increase Jethro’s capacity for concurrent users and queries.
2. When performing frequent incremental loads, it is recommended to run the JethroLoader on a different server.

1. If a large number of queries filter by a specific column (that is not already a partition column) it could be beneficial to pre-sort the input data by such column before it’s loaded into Jethro.

1. When attributes of large dimensions are often used as a filter it is recommended to define them as a JOIN INDEX on the fact table. There is no limit to the number of JOIN INDEXES that can be defined.

1. Jethro is capable of using other storage systems besides Hadoop’s HDFS. These include a local filesystem, cloud storage (eg S3) or network storage (SAN/NAS).
2. When the dataset used with Jethro can fit in a local filesystem it is often the best solution as it avoids Hadoop overhead.

1. When a dimension changes and need to be reloaded you can use Jethro’s Load Overwrite feature. It loads the updated table and only when the process is completed the tables are swapped.

1. Jethro, being a column oriented design, can dynamically add (or drop) columns without having to reload the table. The value NULL will be used for the new column over existing rows

Use Jethro’s “SHOW” SQL command to learn about Jethro internals

• SHOW [SESSION | GLOBAL] PARAM parameter | ALL   (show parameter values)
• SHOW TABLES [EXTENDED | MAINT] (show all tables, size stats, fragmanation)
• SHOW TABLE PARTITIONS table_name (show table’s partition stats)
• SHOW TABLE COLUMNS [FULL] table_name (show column stats)
• SHOW VIEWS [EXTENDED] (show views)
• SHOW LOCAL CACHE (show local file cache usage)
• SHOW ADAPTIVE CACHE (show query result cache)
• SHOW ACTIVE QUERIES (show currently running and queued queries)
• SHOW SCHEMAS (show defined schemas)
• SHOW JOIN INDEXES (show all defined JOIN indexes)