Download Contributed Software

This area lists files that have been contributed to the Infobright Community by Infobright, Infobright employees, and community members.  Users may have their contributions listed here by executing our Contribution Agreement.  These files have not gone through rigorous QA testing at Infobright. 

Database Administration

From Infobright Monitoring to Backup and Recovery, this section contains contributed software designed to aid in Infobright database administration.

1. Infobright Monitoring Script - October 22, 2010

Download File »

Description:

The goal of this scripted utility is to capture system and server statistics, for Infobright versions 3.4 and higher, into a database table on a regular frequency for access by any tool that can connect to Infobright – reporting tools, analytic tools, dev tools like VisualStudio or PHP, etc.
There are two files in the compressed archive for download that do that. One is the DDL for a single table to capture all the statistics (in the ‘test’ schema and ‘brighthouse’ storage engine). It captures 270 different values for reporting, monitoring or trending. The other file is a script that captures the statistics and writes them to the table.

Here is what is captured:

* a timestamp to the second of when the statistics were gathered
* the average number of queries per second from the ‘mysqladmin status’ command output
* 261 other statistics from the ‘mysqladmin extended-status’ command output including 13 Infobright-specific values
* the total, free and used memory values from the ‘top’ command output
* the total, free and used swap values from the ‘top’ command output

In the spirit of open source, both files are simply text files that can be edited and modified as needed or desired; values can be removed or new ones can be added.

2. Backup and Recovery Script - September 3rd, 2010

Download File »

This Linux-only bash shell script performs either a backup of an Infobright instance (all backups of Infobright are of the entire instance – not at the level of individual schemas or databases) or a recovery from a previous backup.

Copy and unzip it wherever you want.  Set permissions so you can execute it.  Run it with no parameters for the following usage message.

Usage:  br-ib < backup|restore > < ib_data_dir > < bu_dir >

NOTE:
The Infobright data directory parameter must match the ‘datadir’ value in /etc/my-ib.cnf.  For example

# The MySQL server
[mysqld]
datadir = /usr/local/infobright/data

The backup directory parameter should not include ‘data’ in its path as it is added automatically.

Running  < path >/br-ib --? (or --h, --help) will give the following description of the script’s nature and behavior.

##########################################################
# Description                                           
#                                                       
# Script to backup and restore an Infobright instance   
#                                                       
# Assumption(s):                                        
# - only one Infobright instance installed              
# - installed with default location for server script   
# - the entire Infobright instance is backed up including
#   the Knowledge Grid and all schemas                  
# - all files are copied "as is" - no 'tar' or 'gzip'   
#                                                       
# Caveats:                                              
# - tested only on 64-bit CentOS 5 with IEE 3.4.2 GA    
# - the 1st parameter is case sensitive
# - there is no logging in this script as of v0.9       
#                                                       
#########################################################
# Usage                                                 
#                                                       
# < path >/br-ib < backup|restore > < ib_data_dir > < bu_dir >  
#    where:                                             
#   < path >        = path to backup/restore script       
#   < ib_data_dir > = Infobright data directory           
#   < bu_dir >     = backup directory                    
#                                                       
##########################################################
# Exit codes                                            
#                                                       
# 0 = successful                                        
# 1 = general, unspecified error                        
# 2 = improper number of parameters provided            
# 3 = improper action flag - 'backup' or 'restore' only 
# 4 = unable to find, read or write Infobright data dir 
# 5 = unable to find, read or write backup directory    
# 6 = backup failed                                     
# 7 = restore failed                                    
#                                                       
#########################################################

3. Simple DB Size - October 6, 2009

Download File »

Perl script

A script to report on database disk usage.

Note: This data size is purely an estimate. The actual data size my vary significantly due to a variety of reasons. This estimation should only be used as a guide instead of fact.

4. LogRotate-IB - July 30, 2009

Download File »

For Linux/Unix use only

Infobright maintains an error log file named bh.err located, by default, in /usr/local/infobright/data.  This log file is not “versioned”, it continues to append to the same file.

This Unix (bash) shell script copies the error log to a backup file and re-initializes the bh.err file.  This script can be added to cron or executed manually.  It can also be run multiple times per day and will append the incremental logs from the same day to the existing file for that day instead of overwriting it if it already exists.  It does not depend on the Infobright/MySQL server to be running as does the option to use FLUSH LOGS in MySQL.  It also allows one to maintain a history of log files instead of a single copy of the contents since the last time it was run (in contrast to FLUSH LOGS).

Log files are not automatically compressed or deleted after a specified number of days, although this is a simple modification that can be added to this script by the Infobright administrator to their specifications.

5. ReOrg - July 30, 2009

Download File »

reorg.sh documentation:

Infobright is designed as a “load and read” analytical database and storage engine.  However, after the execution of DML activities like UPDATE and DELETE (in the Enterprise Edition of Infobright), tables can become fragmented and benefit from reloading which effectively compacts the table by removing all existence of formerly deleted record(s) and their associated delete mask(s).

reorg.sh does just this.  It exports all data from a single, given table in binary file format to the file system, performs an Infobright variation of TRUNCATE on the table, reloads the table with the Infobright Loader, and then removes the export file from the file system.  See the code extract below:

if [ $# -ne 2 ]; then
    echo "Usage: $0

" 1>&2
    exit 2
fi

MYSQL=`locate mysql-ib`

$MYSQL << EOF
-- set up environment
    USE ${1};
    SET @bh_dataformat='binary';

-- export data
    SELECT COUNT(*) FROM ${2};
    SELECT * FROM ${2} INTO OUTFILE '/tmp/${2}.bin';

-- truncate table
    CREATE TABLE X LIKE ${2};
    DROP TABLE ${2};
    CREATE TABLE ${2} LIKE X;
    DROP TABLE X;

-- reload table
    LOAD DATA INFILE '/tmp/${2}.bin' INTO TABLE ${2};
    SELECT COUNT(*) FROM ${2};

-- remove export file
    \! rm -f /tmp/${2}.bin
    \q
EOF

exit 0

In its current form, reorg.sh doesn’t perform any sanity checks such as ensuring there is enough available file system space for the export or validating that the amount of data exported matches the amount of data reloaded.  (COUNT(*) output is sent to STDOUT during execution for manual validation.) 

It also assumes, for example, that the Infobright engine is running, that the MySQL client is named mysql-ib (from the default installation), and that there is only one of them.  But it does not assume any particular location of this file.

This is a script.  Use it “as is” if you are comfortable with its current form or feel free to use it as a template for your own custom reorg utility.

6. Infobright Database Summary PHP Webpage - May 2011

Download File »

Zip File

Unload the contents of this zip file into your web server. Then, navigate to the tools.php page. You must have a web server running along with PHP in order for this page to render. If you need to install a webserver with PHP, consider using LAMP, WAMP, or MAMP stacks

Note: The estimated raw data size is a only an estimate. The actual raw data size may be significantly higher or lower than the estimation amount.

Data Loading

From Oracle to ParaFlex, loading data into Infobright can be easy with these simple tools.

1. ICE Breaker for Oracle V1.0

Download File »

For Windows use only

OVERVIEW – ICE Breaker for Oracle is a Windows program that connects to an Oracle database, and creates a file that contains the necessary commands to migrate from Oracle to Infobright.

 

• Create table statements to create the original Oracle tables in Infobright format, use cut/paste to run this in the mysql-ib command line interface or use a GUI tool like Toad for MySQL, Navicat or SQirreL to run these commands after connecting to the Infobright database. This is independent of the operating system, Linux, Solaris or Windows.
• Extract script statements to that call the 'flat' script to run Oracle SQL*Plus to extract the data. Run these in a Windows command line window. You can run these in parallel for maximum throughput.
• MySQL load commands, use cut/paste to run this in the mysql-ib command line interface or use a tool like Toad for MySQL, Navicat or SQirreL to run these commands after connecting to the Infobright database. This is independent of the operating system, Linux, Solaris or Windows.
• If data is on a different server, then a manual step of moving that data generated by SQL*Plus is required and left up to you on how you want to accomplish that. I would recommend an open source tool like FileZilla that FTP’s in parallel streams or WinSCP.

2. ICE Breaker for SQL Server

Download File »

ICE Breaker for Microsoft SQL Server is a Windows program that connects to a SQL Server database, and creates a file that contains the necessary commands to migrate from SQL Server to Infobright.

3. Connector Core Library

Download File »

The connector core library provides an API for Java developers to interface directly with the Infobright bulk load capability. It is made available for use with the Talend and Pentaho ETL connectors developed by Infobright. It is also for developers who wish to implement new bulk-output connectors for other Java-based ETL tools. This package includes: Full source code, the library (jar file), and API (Javadoc) documentation.

New in this release:

1) Correct handling of embedded backslashes in Community mode.

Please note: Windows is only supported with v3.2.1 (or newer) of ICE or IEE

4. Talend ETL Connector

The connector shows up under Databases -> MySQL in the palette.

Download file for Talend 4.0 and higher »

New features with 4.0

1) Bug fix with end-parentheses going from 3.7 to 4.0
2) This version is not compatible with Talend versions older than 4.0

Download file for Talend 3.7 and earlier »

New features with 3.7

1) update Infobright core library version to 3.4
2) Fix for unescaped embedded backslashes in Community mode

New features with 3.6

           1) Update Infobright core library version to 3.3
           2) Correct checking of string length with multi-byte character set
           3) 5-20x remote load performance improvement via use of LOAD DATA LOCAL INFILE (requires IB 3.5.0+ with Talend running on Linux).

 

New features with 3.5

           1) Update Infobright core library version to 3.2
           2) minor bug fixes

 

New features with 3.4

1. Support for remote load.

 

New features with 3.3

1. Support for Windows with ICE and IEE v3.2.1
2. Compatibility with JDK 1.5 (previously required 1.6)
3. Bug fixes

5. Kettle Connector

Download File »

The Infobright Kettle connector is a Kettle transformation step that is designed to load data into an ICE table.  Please note that this release is for Linux distributions of ICE only.

6. ParaFlex Loader

Download File »

This is a tool used by the Infobright Implementations Team.  It is a shell script that that will manage a variable number of parallel load processes for ICE and IEE.  A single control file is used to allow simple configuration of the queue.

7. ICE Breaker for MySQL

Download File »

ICE Breaker Documentation (MySQL)

ICE Breaker for MySQL is a Unix shell script  that connects to a MySQL database, and creates a file that contains the necessary commands to migrate from MySQL to Infobright.

Database Migration / Conversion to Infobright

These tools help you move from a previous database to Infobright.

1. Tutorial: Data Transfer from MySQL to Infobright

Download File »

Tutorial  on how to get data out of mysql into a CSV file, work on the data using awk and finally loading it into Infobright.

The emphasis is on the checking and discovery of the data in the CSV file. 

 

2. Innodb Export

Download File »

Script to export/import data from InnoDB table in one MySQL instance to an Infobright table in another.       

Assumption(s): table to be exported and imported have  same name  the whole table is exported - edits will  need to be made for higher selectivity   

Caveats: there is no validation of the "type" of   either schema  there are very few sanity checks in this  script such as file system space and  record counts out and in.

3. MyISAM Export

Download File »

For Linux/Unix use only

This is a Unix (bash) script that works on a table-by-table basis and exports data from a MyISAM table into a data file and imports (via LOAD) into an Infobright table of the same name and schema.  It could be adapted for multiple tables can be purged using the  same filter criteria.

Development

1. Sample Infobright Database

Infobright has created a sample database, carsales, to allow you to evaluate the Infobright Analytic Database, both ICE and IEE, and execute queries against significant volumes of data.  The carsales database represents a fictional automobile dealer network and contains 10,000,000 sales records in its central fact table: fact_sales.

The fact_sales table records information about each sale.  As part of a transformation process, the dealer identifier, marketing area, and sales area were appended to each transactional record to form the fact record.  Every car transaction falls into a sales area and a market area.  The sales and market areas may overlap, so separate tracking of each is required.

The sample download includes:

• documentation

• enhanced logging

• enhanced (optional) MyISAM table objects

• a suite of 110 SQL commands including queries, VIEWs and a stored procedure

NOTE

1. The compressed sample database download is about 10X the size of the software download (at ~630MB) and takes up almost 3GB on disk when uncompressed.  

2. It will consume approximately another 5GB of database disk space if the MyISAM tables are loaded, only about 1GB if only Infobright tables are loaded.  

3. It will take time to download.  Evaluators will need appropriate disk space for the download, the data files, and the loaded database itself.

 

Query Descriptions
Linux README
Windows README
Linux Database
Windows Database

2. Oracle Date Conversion to 'YYYY-MM-DD'

Download File »

When data within your flat file uses an improper date format (such as 'm/d/yyyy' or 'dd-MON-yyyy'), one must convert the date to the proper format ('YYYY-MM-DD').  Written in perl, this script takes raw data through standard in and returns values in standard out.  It converts the date column to the proper format. 

Usage: fixdate.pl [-F]
Delimiter defaults to ','

3. Rename Table

Download File »

Usage: rename_table

rename_table will do the same as the alter SQL statement

alter table

rename to

4. Truncate Table Script

Download File »

The SQL function TRUNCATE TABLE tbl_name quickly and efficiently removes all data from a table while retaining all of its other attributes such as indices, constraints and permissions.  Infobright does not implement indices nor constraints so those are not issues in this environment.  Permissions are associated with a table name and are preserved with this utility.

The MySQL Administrator’s Guide, page 44 of the chapter on Optimization, says:

6.2.18 Speed of DELETE Statements
...
To delete all rows from a table, TRUNCATE TABLE tbl_name is faster than DELETE FROM tbl_name.

However, Infobright implements TRUNCATE TABLE tbl_name with the DELETE operation which does not deliver the normally expected performance, especially on large tables.

Therefore, this script, truncate.sh, performs an effective TRUNCATE TABLE tbl_name by creating an empty copy of the table, dropping the table ( DROP TABLE tbl_name), and then creating a copy of the empty table with the same name (effective the same as ALTER TABLE old_tbl_name RENAME new_tbl_name, which is not yet implemented in Infobright – see rename_table.sh script on the Contributed Software page).  This is quick and efficient and works exactly as would be expected via the SQL API – except from the operating system command line.

You can save the script under any name you wish but we suggest truncate.sh.  The synax/usage is as follows:

Make sure to set the appropriate ownership and permissions for your environment (perhaps, mysql mysql rwxr-xr-x (755)) and that the script is either in the user’s path or is executed with the explicit path to where it was saved.

5. Truncate Table Stored Procedure

Download File »

The SQL function TRUNCATE TABLE tbl_name quickly and efficiently removes all data from a table while retaining all of its other attributes such as indices, constraints and permissions.  Infobright does not implement indices nor constraints so those are not issues in this environment.  Permissions are associated with a table name and are preserved with this utility.

The MySQL Administrator’s Guide, page 44 of the chapter on Optimization, says:

6.2.18 Speed of DELETE Statements
...
To delete all rows from a table, TRUNCATE TABLE tbl_name is faster than DELETE FROM tbl_name.

However, Infobright implements TRUNCATE TABLE tbl_name with the DELETE operation which does not deliver the normally expected performance, especially on large tables.

Therefore, this stored procedure, truncate_table(), performs an effective TRUNCATE TABLE tbl_name by creating an empty TEMP TABLE copy of the table, dropping the table (DROP TABLE tbl_name), and then creating a copy of the empty TEMP TABLE with the same name (effectively the same as ALTER TABLE old_tbl_name RENAME new_tbl_name, which is not yet implemented in Infobright – see rename_table.sh script on the Contributed Software page).  This is quick and efficient and works exactly as would be expected via the SQL API – except from the operating system command line.

You must execute the SQL file truncate_sp.sql in any database where you wish to use this stored procedure. 

6. MySQL Proxy / Infobright CTAS Lua

Download File »

For MySQL Proxy 0.61 only

Support for Create Table As Select (CTAS) through MySQL Proxy Server.  Requires v0.61 of the MySQL Proxy - downloaded separately from MySQL.

New Features with this release

1. Added additional commenting
2. Added standard logging function
3. Dropped the temporary view at the end
4. Inline view for exporting. Now, you can select between inline view or direct select ... output file options.

And please note that, because MySQL and infobright has some differences between data types, there is a function in code that does this conversion. For example MySQL has decimal (38,0) but in Infobright its maximum size is 18. So the program must be extensively tested on several different CTAS’es. Probably need to have change this function according to the results.

7. Fix Comments in MySQL 5.1.40

Download File »

Versions of Infobright prior to v3.3.1 were bundled with MySQL 5.1.14 and observed the 'double-dash' (--) syntax to represent a COMMENT.  With the release of v3.3.1 (and later) based on MySQL 5.1.40, the syntax support for COMMENT was changed to 'double-dash-whitespace' (-- ).

This is a Unix/Linux bash shell script that uses the 'sed' command to change all double-dashes to double-dash-space and any any double-dash-space-spaces back to double-dash-space.  This is a very simple script with all assumptions documented in the script header.

Cloud

These documents/scripts aid you in using Infobright on the Cloud

1. Deploying ICE on the Cloud

Download File »

This document details how to install ICE within the Amazon Elastic Compute Cloud (EC2.)