Toad For Cloud Databases (eclipse) Getting

Transcript

Toad® for Cloud Databases (Eclipse) 1.2 Getting Started Guide © 2010 Quest Software, Inc. ALL RIGHTS RESERVED. This guide contains proprietary information protected by copyright. The software described in this guide is furnished under a software license or nondisclosure agreement. This software may be used or copied only in accordance with the terms of the applicable agreement. No part of this guide may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying and recording for any purpose other than the purchaser’s personal use without the written permission of Quest Software, Inc. The information in this document is provided in connection with Quest products. No license, express or implied, by estoppel or otherwise, to any intellectual property right is granted by this document or in connection with the sale of Quest products. EXCEPT AS SET FORTH IN QUEST'S TERMS AND CONDITIONS AS SPECIFIED IN THE LICENSE AGREEMENT FOR THIS PRODUCT, QUEST ASSUMES NO LIABILITY WHATSOEVER AND DISCLAIMS ANY EXPRESS, IMPLIED OR STATUTORY WARRANTY RELATING TO ITS PRODUCTS INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, OR NONINFRINGEMENT. IN NO EVENT SHALL QUEST BE LIABLE FOR ANY DIRECT, INDIRECT, CONSEQUENTIAL, PUNITIVE, SPECIAL OR INCIDENTAL DAMAGES (INCLUDING, WITHOUT LIMITATION, DAMAGES FOR LOSS OF PROFITS, BUSINESS INTERRUPTION OR LOSS OF INFORMATION) ARISING OUT OF THE USE OR INABILITY TO USE THIS DOCUMENT, EVEN IF QUEST HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. Quest makes no representations or warranties with respect to the accuracy or completeness of the contents of this document and reserves the right to make changes to specifications and product descriptions at any time without notice. Quest does not make any commitment to update the information contained in this document. If you have any questions regarding your potential use of this material, contact: Quest Software World Headquarters LEGAL Dept 5 Polaris Way Aliso Viejo, CA 92656 www.quest.com email: [email protected] Refer to our web site for regional and international office information. Patents Toad for Cloud Databases contains patent pending technology. Trademarks Quest, Quest Software, the Quest Software logo, Benchmark Factory, Toad, T.O.A.D., Toad World, and vToad are trademarks and registered trademarks of Quest Software, Inc in the United States of America and other countries. For a complete list of Quest Software's trademarks, please see http://www.quest.com/legal/trademark-information.aspx. Other trademarks and registered trademarks used in this guide are property of their respective owners. Toad for Cloud Databases 1.2 Getting Started Guide November 2010 Table of Contents About Toad for Cloud Databases 6 What does Toad for Cloud Databases do? 6 How does Toad for Cloud Databases work? 6 Which Cloud databases are supported? 7 Installation Toad for Cloud Databases 9 9 Install Toad for Cloud Databases 9 Uninstall Toad for Cloud Databases 9 The Data Hub 10 Install the Data Hub - Windows 10 Install the Data Hub - Fedora 11 Start Toad for Cloud Databases 15 Connect to the Remote Data Source 16 Using the Data Hub 16 Connect to the Data Hub 16 Map to the Remote Data Source 17 Map Remote Objects To SQL Tables 19 Connect Direct to Hive 23 Work with SQL Tables 25 View Object Details 25 Execute SQL Commands 26 Using the Toolbar for the SQL Editor 27 Drag and Drop 28 Copy Tables 28 How to Copy Table 30 Considerations on Copy Table 32 Toad for Cloud Databases (Eclipse) Getting Started Guide 5 Table of Contents When Copying to and from Azure Table Services 32 When Copying from an RDBMS to a Cloud Database 33 When Copying to an Oracle Database 33 When Copying from One ODBC Database to Another 33 Considerations when working with SQL tables 33 Appendix: Contact Quest 36 Contact Quest Support 36 Toad Community 36 Contact Quest Software 36 About Quest Software 36 Index 38 1 About Toad for Cloud Databases What does Toad for Cloud Databases do? What is a Cloud database? Cloud databases: l Process huge volumes of data. The volume of data produced by a web site like Google, Twitter and Facebook for example is too enormous to be processed by a conventional relational database. At the time of writing this guide the daily message volume of Twitter was around 7 terabytes per day. l Host data in the Internet cloud. Cloud databases are accessed via the Internet. l Are also called NoSQL databases because their interface is not native to SQL. To query, access and modify the data requires code that can be tedious, expensive and time consuming even for software developers to write. Toad for Cloud Databases enables users to use SQL to: l Work with data in a Cloud database. l Join a Cloud database to a relational database on the personal computer. l Copy tables from one Cloud database to another or between a Cloud database and a relational database on the personal computer. SQL is an industry standard commonly used by data analysts, data administrators and business intelligence (BI) analysts. How does Toad for Cloud Databases work? There are two key components to Toad for Cloud Databases. The first is the Eclipse plugin. The Eclipse plugin is used to access the Cloud database and write and execute SQL statements. Eclipse is an open source platform. The second component is the Data Hub. It translates SQL statements submitted through the Eclipse plugin into a language understood by the Cloud database, and returns results in the familiar tabular row and column format. It can be installed on the local computer or another computer with network access. Toad for Cloud Databases (Eclipse) Getting Started Guide About Toad for Cloud Databases Which Cloud databases are supported? The following Cloud database technologies are supported: Technology Service Provider For further information Azure Table Services Microsoft Introduction to Windows Azure Cassandra Apache OpenSource Apache Cassandra home page SimpleDB Amazon Home page Table Services Concepts Getting Started Guide Hadoop HBase OpenSource: based on Google technologies Home page 7 Toad for Cloud Databases (Eclipse) Getting Started Guide About Toad for Cloud Databases Technology Service Provider For further information Hive Apache OpenSource Apache Hive home page ODBCcompliant relational databases - Though not a Cloud database, support for ODBC-compliant relational databases enables Toad to join data from a local relational database to a remote Cloud database in a single SQL query. 8 2 Installation Toad for Cloud Databases Install Toad for Cloud Databases Notes: l Ensure Eclipse 3.5 or higher is installed and operational in the computer system. Ensure the computer system satisfies all the requirements to run Eclipse, such as Java JRE installed before you install Toad for Cloud Databases. l If Toad for Cloud Databases has been previously installed then uninstall it before you install it again. See "Uninstall Toad for Cloud Databases" (page 9) for more information. 1. Start Eclipse. 2. From the Eclipse toolbar select Help | Install New Software... 3. Fill in the Install dialog: a. In the Work with field type http://toaddownload.quest.com/toadforcloud/eclipse/releases/1.2 b. Expand Toad Extension for Eclipse if Group items by category is selected. c. Select Toad for Cloud Databases. 4. Eclipse will analyze the Toad plugin to ensure all dependencies can be satisfied. 5. Accept the Quest Software license agreement. 6. The installation completes. 7. Restart Eclipse. Uninstall Toad for Cloud Databases This follows the standard procedure to uninstall an Eclipse plugin. 1. Start Eclipse. 2. From the Eclipse toolbar select Help | About Eclipse 3. Click Installation Details. 4. Select Toad for Cloud Databases. 5. Click Uninstall... | Finish 6. Restart Eclipse. Toad for Cloud Databases (Eclipse) Getting Started Guide 10 Installation The Data Hub Install the Data Hub - Windows Follow these instructions to download and install the data hub to a Windows computer. The data hub may be installed on the same machine as Toad for Cloud Databases or a machine with a network connection to Toad for Cloud Databases. Installer Type Supported Platforms Windows l Windows 7 (32-bit and 64-bit) l Windows Vista (32-bit) l Windows XP (32-bit) Download Download the data hub installer to the Windows machine that will host the Data Hub. The installer is available from the Toad for Cloud Databases community web page: http://toadforcloud.com/ Install 1. Run the Data Hub installer. 2. Agree to the license agreement. 3. Indicate whether connection to the Internet is direct or via a proxy server. Provide details of the proxy server if applicable. Notes: l The proxy address is the DNS or IP address of the proxy server within your organization. HTTP and HTTPS secure proxy servers are supported. The proxy port is the port on which the proxy server operates. The standard proxy port number is 8080. Sometimes port 80 is used. Less commonly an entirely different port may be used. l If the proxy server is authenticated then follow this work-around. After the Data Hub installer has finished, manually authenticate the Windows http_proxy or https_proxy environment variable. Edit the environment variable to be of the form http://username:password@proxyAddress:proxyPort where username and password authenticate the proxy server within your organization. 4. Make a note of the connecting port number. You will use it to connect to the data hub. 5. Create a password to connect to the data hub. 6. Accept or change the installation directory. Toad for Cloud Databases (Eclipse) Getting Started Guide 11 Installation Upon successful installation, the data hub will run as a service that starts automatically when Windows starts. Install ODBC drivers (if required) To connect to ODBC data sources it is required that supporting ODBC drivers be installed in the same machine as the data hub. The data hub supports the following drivers: l Oracle: Oracle 11g instant client Basic + ODBC Supplement l SQL Server, SQL Azure: SQL Server Native Client 10.0 l MySQL: MySQL Connector/ODBC 5.1 Uninstall Follow these instructions to uninstall the Data Hub if required. 1. Open Windows Control Panel. 2. Select Programs and Features. (On Windows XP select Add/Remove Programs.) 3. Click Quest Data Hub | Remove | Yes. Install the Data Hub - Fedora Follow these instructions to download and install the data hub to a Fedora computer. The data hub may be installed on the same machine as Toad for Cloud Databases or a machine with a network connection to Toad for Cloud Databases. Installer Type Supported Platforms Fedora l Fedora 14 Linux (32-bit and 64-bit) l Fedora 13 Linux (32-bit and 64-bit) Download Run the following command as root: l rpm -Uvh http://toaddownload.quest.com/toadforcloud/yum/Fedora/toadforcloudrelease-1.0-1.noarch.rpm Install Run the following command as root: l Notes: yum install datahub Toad for Cloud Databases (Eclipse) Getting Started Guide 12 Installation l Dependencies such as mysql-server are installed automatically. l The data hub starts automatically on Fedora reboot. Following install, to start the data hub without reboot enter the command service datahub start l Additional installation instructions follow if required. They are for installation of ODBC drivers, for when the data hub is to be accessed remotely and when connection to the Internet is via a proxy server. Update If Fedora automatically checks for updates then updates to the data hub will be found as a matter of course. To explicitly check now if there is an update, run the following command as root: l yum update datahub Uninstall Run the following command as root: l rpm -e datahub Additional Installation Instructions Install ODBC Drivers (if required) To connect to ODBC data sources it is required that supporting ODBC drivers be installed in the same machine as the data hub. Toad for Cloud Databases includes specific support for connections to Oracle, SQL Server, SQL Azure and MySQL. You may also manually enter a connection string to use any generic ODBC driver that is installed on the same host as the data hub. ODBC data source Installation of drivers Oracle The simplest way to obtain the Oracle ODBC driver is to install the Instant Client, which is specifically licensed to allow users to download and deploy it without charge. The Instant Client is freely available from http://www.oracle.com, together with installation instructions. Note: For Oracle to appear as an option when mapping a data source the name of the Oracle driver entry in the ODBC configuration file /etc/odbcinst.ini must contain the text Oracle. Letter case is not significant. SQL  Server and SQL  Azure There are several commercial SQL Server drivers. The data hub was developed and tested with the FreeTDS driver. This driver is available through the yum repositories. Run the following command to install it. Follow the prompts. sudo yum install freetds Once FreeTDS is installed it must be manually configured in ODBC. Login as root and edit the file /etc/odbcinst.ini by adding the following entry: Toad for Cloud Databases (Eclipse) Getting Started Guide 13 Installation ODBC data source Installation of drivers [FreeTDS] Driver = /usr/lib/libtdsodbc.so.0 Driver64 = /usr/lib64/libtdsodbc.so.0 Note: For SQL Server and SQL Azure to appear as options when mapping a data source the name of the SQL Server driver entry in the ODBC configuration file /etc/odbcinst.ini must contain the text FreeTDS or SQL Server. Letter case is not significant. MySQL The MySQL driver is available through the yum repositories. Run the following command to install it. Follow the prompts. sudo yum install mysql-connector-odbc Note: For MySQL to appear as an option when mapping a data source the name of the MySQL driver entry in the ODBC configuration file /etc/odbcinst.ini must contain the text MySQL. Letter case is not significant. On remote access to the data hub Follow these steps if the data hub is to be accessed from a Toad for Cloud Databases installation on a separate computer from the data hub. 1. Ensure MySQL accepts root user connections from other hosts a. Start the data hub. The data hub starts automatically on Fedora reboot. To start the data hub without reboot: service datahub start b. Login to the MySQL instance of the data hub: mysql -u root --port=3566 --protocol=TCP c. Ensure MySQL accepts root user connections from other hosts (password is the password to the data hub): grant all on *.* to root@'%' [identified by 'password'] 2. Ensure port 3566 is open to remote access. If the Fedora machine uses a proxy server to connect to the Internet Follow these instructions after you install the data hub but before you start the data hub. 1. Login as root. 2. Edit /etc/sysconfig/datahub Toad for Cloud Databases (Eclipse) Getting Started Guide 14 Installation 3. Add the following lines to specify the proxy server to use for HTTP requests: http_proxy=http://proxy address:proxy port/ export http_proxy Add the following lines to specify the proxy server to use for HTTPS requests: https_proxy=http://proxy address:proxy port/ export https_proxy Note: For authenticated proxies the value of the environment variable is of the form http://username:password@proxyAddress:proxyPort a. The username and password are required for authenticated proxies. They are the username and password to the proxy server within the user's organization. b. The proxyAddress is the DNS or IP address of the proxy server within the user's organization. c. The proxyPort is the port on which the proxy server operates. The standard proxy port number is 8080. Sometimes port 80 is used. Less commonly an entirely different port may be used. 4. Save the file. 5. Start or restart the data hub. Note: The data hub starts automatically on Fedora reboot. To restart the data hub without reboot enter the command service datahub start Toad for Cloud Databases (Eclipse) Getting Started Guide 15 Start Toad for Cloud Databases Start Toad for Cloud Databases Note: These instructions assume prior installation of Toad for Cloud Databases. See "Install Toad for Cloud Databases" (page 9) for more information. 1 Start Eclipse on your computer system. - 2 Is the Toad for Cloud Databases perspective open? To open the Toad for Cloud Databases perspective, from the Eclipse toolbar select Window | Open Perspective | Other... | Toad for Cloud Databases 3 Is the data hub connected? The data hub may be already connected and visible from the Toad Explorer page. See "Connect to the Data Hub" (page 16) for more information. Hive connections do not use the data hub. See "Connect Direct to Hive" (page 23) for more information. 4 Has the remote data source been mapped to SQL tables? Tip: The data hub saves all mappings to the remote data source. If you have previously connected to the remote data source then connect to the same data hub. See "Map to the Remote Data Source" (page 17) for more information. 5 Work with SQL tables. View Object Details (page 25), Execute SQL Commands (page 26), Copy Tables (page 28). 3 Connect to the Remote Data Source Using the Data Hub Connect to the Data Hub Data hubs have the following icon in the Toad Explorer pane . 1. Click Create a new Data Hub. 2. Configure as prompted: Field Description Host address Type localhost to use the data hub setup on this computer. Type a network address to use a data hub setup elsewhere. Port number Type the port number required to connect to the data hub. Password Type the password required to connect to the data hub. Automatically connect to this Data Hub on start-up Select to connect to this data hub automatically on opening the Toad for Cloud Databases perspective. Notes: l Install the data hub before you try to connect to it. l The data hub may be installed on the local computer or on a network accessible to the local computer. A network location for the data hub works when accessing the mappings to the remote data source from multiple locations or when multiple people access the mappings. l You can connect to multiple data hubs in the Toad Explorer pane. l The data hub saves all mappings to the remote data source. If you have previously connected to the remote data source then connect to the same data hub. l If the data hub icon is then the data hub is disconnected. Select the data hub and click to connect. Click to disconnect. Toad for Cloud Databases (Eclipse) Getting Started Guide Connect to the Remote Data Source Map to the Remote Data Source 1. Select the data hub. If a data hub cannot be selected see Connect to the Data Hub (page 16). 2. Click Map a new data source to this Data Hub. 17 Toad for Cloud Databases (Eclipse) Getting Started Guide 18 Connect to the Remote Data Source 3. Configure as prompted: Option Description Data source name Enter a free-form name to the data source. This name is used for display purposes in Eclipse. Data source type Select a data source type from the list. Parameters of the data source The parameters vary with the data source type. l Azure Table Services - Enter the username and password to connect to the Azure source. l Cassandra - Enter the host, port, keyspace, username and password to connect to the Cassandra source. l HBASE - Enter the Stargate host and port of the HBASE data source. l SimpleDB - Enter the username (Access ID) and secret key to connect to the SimpleDB source. l ODBC database types as per drivers supported by the data hub and installed on the same machine as the data hub. Additional information on the form fields is available for: l SQL Server - See "Additional Information Mapping to a SQL Server Data Source" (page 19) for more information. Assistance for ODBC drivers is available. l l See "Install the Data Hub - Windows" (page 10) for more information. l See "Install the Data Hub - Fedora" (page 11) for more information. Generic ODBC - Use to connect to an ODBC database not available through other options. Toad for Cloud Databases (Eclipse) Getting Started Guide 19 Connect to the Remote Data Source Additional Information - Mapping to a SQL Server Data Source Parameter Value ODBC driver SQL Server Instance name The name of the database instance. Database name The name of the database. Use Windows integrated security Select to use Windows integrated security. User ID The User ID to connect to the database. Note: When not selected, enter a value for the User ID and Password. Note: A value is required when Windows integrated security is not selected. Password The Password to connect to the database. Note: A value is required when Windows integrated security is not selected. Notes: l A data hub can connect to multiple data sources. l To disconnect the data source from the data hub, select the data source and click l Connections to Azure Table Services, Cassandra, Hadoop HBase and Hive are via HTTP. Connections to SimpleDB are via HTTPS. . Map Remote Objects To SQL Tables Within the data source are organizational units similar to an SQL table. For Azure Table Services, Cassandra, HBASE and ODBC they are called tables. For SimpleDB they are called domains. To work with a remote organizational unit in Toad it must be mapped to an SQL table. 1. Within the data source, open Remote Objects. 2. Right click a remote object| Map Remote Object. Toad for Cloud Databases (Eclipse) Getting Started Guide 20 Connect to the Remote Data Source 3. Configure as required: Option Description Data source The data source type. This was defined when the data source was mapped. See "Map to the Remote Data Source" (page 17) for more information. Remote object This is the selected remote object. It cannot be changed. Table name The name of the table as it appears in Eclipse. By default the name of the remote object is also the table name. Columns By default all fields in the data object are mapped to the table. Field names and data types are as per the data object. The dialog shows: l Remote Field - The name of the field in the remote object. l Local Name - The name of the field in the local table. l Data Type - The data type of the field in the local table. l Not Null - Selected when Null values are not allowed in this field of the local table. Actions can be taken to choose which fields to map, rename a field and Toad for Cloud Databases (Eclipse) Getting Started Guide Connect to the Remote Data Source Option Description change a field's data type and required status. Caution: Issues can arise when taking action on a field with an identifier data type. See "Taking Actions on Fields with identifier Data Type" (page 22) for more information. To not map a field: a. Select the field to remove. b. Click . Note: After fields have been removed, you can restore them to the list with the function. To rename a field: a. Click the name in the Local Name list. b. Rename. To change a field's data type: Select an alternative. Caution: The Map Remote Object function fails if the data does not match the data type. For example, setting the field's data type to integer causes Map Remote Object to fail if the field has nonnumeric data. To change a field's required status: Click Not Null. When Not Null is selected a data value is required in each record of the field. Caution: The Map Remote Object function fails if Not Null is selected for a field that has null data. 21 Toad for Cloud Databases (Eclipse) Getting Started Guide 22 Connect to the Remote Data Source Option Description Subtables HBase and Cassandra utilize a column family construct for data organization. In essence, a column family is a grouping element that sits between the level of table and column, and groups one or more related columns together. Toad for Cloud Databases maps dynamic column families as sub-tables. We use the term dynamic to denote that either the family names or column names are actually data rather than static identifiers. In these cases, the columns in the column family are abstracted out to a child table that is subordinate to the main table that is being mapped. The main table and its sub-tables can then be relationally joined using the identifier fields from the main table. Note: More information on column families can be found on the Toad for Cloud Database WIKI page in the articles Column Families 101 for HBase and Cassandra Column Families. To add a sub-table mapping: a. Click . b. Enter a value for the Column family name and Subtable name. Tip: Click Preview to verify the mappings are as you expect. Note: Use the appropriate toolbar buttons to edit and remove sub-table mappings if required. Preview Click to show the current table and sub-table mappings using data from the remote data source. Click to verify the mappings you have created and see where you may need to adjust the mappings to create the desired relational tables. Considerations Taking Actions on Fields with identifier Data Type Columns with name id and data type identifier are generally part of the table's metadata and should not be removed. Data source Organizational unit Number of columns with data type identifier Azure Table Services Table 2 Cassandra Column Family 1 HBASE Table 1 Toad for Cloud Databases (Eclipse) Getting Started Guide 23 Connect to the Remote Data Source Data source Organizational unit Number of columns with data type identifier SimpleDB Domain 1 ODBC Table 1 or more When the Data Source is an Oracle Database l Ensure that for the local table the data object name and all field names are in UPPERCASE. The Data Hub receives column metadata from Oracle in uppercase, regardless of the letter case the metadata was created in. l Toad does not recognize a 64-bit version Oracle ODBC on a 64-bit Operating System. Use a 32-bit version of the Oracle client (Oracle server, client or instant client [Basic and ODBC] ). When the Data Source is a Cloud Database The data hub requires Internet HTTP or HTTPS access to the Cloud database. When there is no Internet HTTP / HTTPS access the data hub may experience connection time outs on attempts to map a data source to the Cloud database. If Internet access is through a HTTP / HTTPS proxy server then setup is required as per the instructions documented at installation. l See "Install the Data Hub - Windows" (page 10) for more information. l See "Install the Data Hub - Fedora" (page 11) for more information. Note: If you see an error like Microsoft Azure requires client applications to use HTTP 1.1 then it is likely your proxy server has converted the Data Hub to use HTTP version 1.0. By default the Data Hub uses HTTP version 1.1. Connect Direct to Hive Use these instructions to connect direct to a Hive database. On successful connection you can use Toad to execute commands in Hive's Query Language (Hive QL). 1. Click the arrow alongside Create a new Data Hub. 2. Select Connect to Hive. 3. Configure as prompted: Field Description Host address Type localhost to use the Hive database setup on this computer. Type a network address to use a Hive database setup elsewhere. Toad for Cloud Databases (Eclipse) Getting Started Guide 24 Connect to the Remote Data Source Field Description Port number Type the port number required to connect to the Hive database. User If required, type the username to connect to the Hive database. Password If required, type the password to connect to the Hive database. Notes: l Direct hive connections in the Toad Explorer pane are grouped under have icon . l Some Toad functions are not available when connecting direct to hive. See the help pages specific to those functions for more information. and 4 Work with SQL Tables View Object Details l Double click on the table in Toad Explorer. Note: Tables in the data hub and hive connections have icon. This opens a read-only view of the table, showing column specifications and data values. Toad for Cloud Databases (Eclipse) Getting Started Guide 26 Work with SQL Tables Execute SQL Commands l Right click the table in Toad Explorer and select Launch SQL Editor. Note: Tables in the data hub and hive connections have This opens the table in the SQL editor. icon. Toad for Cloud Databases (Eclipse) Getting Started Guide 27 Work with SQL Tables Using the Toolbar for the SQL Editor Toolbar Description Click to execute the SQL commands. Execute SQL Note: When complete, a Success or Failure notice will appear in the Messages pane. While SQL commands are executing, click to cancel their execution. Cancel Select text in the SQL Editor and click Cut. Cut Select text in the SQL Editor and click Copy. Copy Cut or Copy text in the SQL Editor. Paste Click the cursor where you would like to paste the text. Click Paste. Toad for Cloud Databases (Eclipse) Getting Started Guide 28 Work with SQL Tables Note: You can Cut, Copy and Paste from one SQL editor pane to another. Drag and Drop Tips: l Select a table in Toad Explorer and drag it into the SQL Editor to simply create an INNER JOIN. l Select a column in Toad Explorer and drag it into the SQL Editor to simply reference the column in the SQL editor. Copy Tables A mapped table can be copied to any mapped data source in the Data Hub. Examples are: l Copy a local RDBMS table to a Cloud database in development of a Cloud application. l Copy a mapped table from a Cloud database to a local data warehouse. Toad for Cloud Databases (Eclipse) Getting Started Guide 29 Work with SQL Tables l Copy a mapped table from one Cloud database to another. l Copy tables between local RDBMS. Caution: This release of Toad for Cloud Databases copies data between databases in a single atomic operation. This approach is most efficient for small or medium sized tables, but is not always practical for large or massive data sets. Specifically, there is no way for Toad to provide feedback during the copy operation and - in the case of copies to relational databases - there may be significant demands on rollback/undo segments or transaction logs during the operation. A future release of the product will provide a copy facility which supports incremental, restartable data transfers with feedback during long operations. Notes: l These instructions assume prior formation of Tables in the Toad Explorer. See "Map Remote Objects To SQL Tables" (page 19) for more information. l This function requires connection through a Data Hub. It is unavailable for direct Hive connections. Toad for Cloud Databases (Eclipse) Getting Started Guide 30 Work with SQL Tables How to Copy Table 1. Right click the table to copy and select Copy Table. 2. Select the To data source. The table is copied to this data source. Choose from any mapped data source in the Data Hub. 3. Configure as follows: Option Description To remote object The table is copied to this object. By default the object has the same name as the table to be copied. To table name The copied table in Eclipse has this name. By default it is the same name as the table being copied. To column family Data source technologies that support column families usually require this. For example: When copying to HBase a column family name is required. Source Column / Target Column / Target Field/Data Type / Not Null By default all columns are copied, their names and data type are unchanged. There may be reason to configure the following fields. See "Considerations on Copy Table" (page 32) for more information. l Source Column The name of the column or field in the table being copied. l Target Column The name given to this column (or field) in the copied table in Eclipse. l Target Field The name given to this column (or field) in the copied table in the remote data source. l Data Type The data type of this column (or field) in the copied table. l Not Null Selected when Null values are not allowed in this column (or field) in the copied table. Actions can be taken to choose which fields to copy, rename a field and change a field's data type and required status. Caution: Ensure you are familiar with the target data source requirements when taking action on a columns with name id and data type identifier. To prevent one or more columns from being copied: a. Select the column to remove. Toad for Cloud Databases (Eclipse) Getting Started Guide Work with SQL Tables Option Description b. Click . Note: After columns have been removed, you can restore them to the list with the function. To rename a column: Columns may need to be renamed, particularly when copying between technologies with different naming conventions. a. Click on the name in Target Column. b. Edit the old name and create a new name. To change a column's Data Type a. Select the column. b. Select a new Data Type. Caution: The copy operation fails if the data does not match the new data type. For example, changing the data type to integer causes copy to fail if the column has non-numeric data. To change a column's required status a. Select the column. b. Select Not Null. Caution: The copy operation fails if Not Null is selected for a column that has null data. 31 Toad for Cloud Databases (Eclipse) Getting Started Guide 32 Work with SQL Tables Option Description Filter copied rows By default all records are copied, up to the number specified in the Limit copied rows to configuration. To filter the records to be copied: a. Click inside the Filter Copied Rows box. b. From Source Column select the column to which the filter should be applied. c. From Operator select the SQL operator (for example LIKE or <>). d. From Filter Value enter the value to be compared to the Source Column using the Operator. For example Age >= 18 The filters are compiled into an SQL WHERE clause. Multiple filters are combined with an AND operator. A row is copied across when every filter condition is matched. Limit copied rows to By default, a maximum of 100,000 records are copied. Show Copy SQL Click to show the SQL statement defining the copy. Caution: This is a protective mechanism. A Cloud database may have tens of millions of records. It is prudent to guard against copy operations that take days to complete and generate unwarranted data storage costs to the Cloud provider. Note: The copy operation will run as a Toad Background Process. After you click OK, the Copy Table window will remain open until copying is complete. Considerations on Copy Table When Copying to and from Azure Table Services Tables in Azure have two columns of data type identifier: a partition key and a row key. Take care in selecting the partition and row keys when copying to and from Azure tables. The partition key is a coarse grain value that groups the rows into discrete subsets of the data that Azure can balance across multiple nodes. (HBase and SimpleDB partition the data automatically but Azure requires the user do it manually.) The row key uniquely identifies each row within the partition. As a good practice the row key should uniquely identify the row within the table. When copying a table from Azure to a non-Azure target, and the target requires the table have one identifier, change the partition key data type away from identifier. This leaves the row key Toad for Cloud Databases (Eclipse) Getting Started Guide 33 Work with SQL Tables as the only column data type identifier. The table can now be copied if the row key is unique. If the row key is not unique then identify another column in the Azure table that is unique to be data type identifier. When copying from a non-Azure table to an Azure target, a column should be selected as the partition key. If a partition key is not selected then an empty string is assigned as the partition key for each row. The two gigabyte limit that Azure enforces for each partition could be exceeded - depending upon the volume of data copied. To set a partition key: a. Select the column to use as the partition key. It should divide the table's data into reasonably balanced groups. Suitable candidates are geographical states or regions and departments. b. For the partition key - set the data type to identifier. c. For the partition key - select Not Null. A value is required in each row. d. Move the column identified for the partition key to the top of the list. Use the arrows to the right of the column grid. Toad uses the first identifier column as the partition key and the second as the row key. When Copying from an RDBMS to a Cloud Database Before a table can be copied from an RDBMS to a cloud database, one column of the RDBMS table must be identified as that which has a unique row key. If no such column exists, then a possible workaround is to create a view in the source database that manufactures a unique row key by concatenating two or more columns. This view can be used as the source of the copy operation. When Copying to an Oracle Database When copying to an Oracle database ensure the target remote data object and field names are in UPPERCASE. The Data Hub receives column metadata from Oracle in uppercase, regardless of the letter case the metadata was created in. When Copying from One ODBC Database to Another Temporary Error: 4200 Cannot modify row with no identity fields may occur when copying a table with no primary key from one ODBC database to another. Although it is possible to create an ODBC table with no primary key it is not good practice to do so. Considerations when working with SQL tables Network Data Transfer A query returning a large number of records from a cloud database will take a long time to execute. The aim of the Quest Data Hub is to provide on response times as the worst possible Toad for Cloud Databases (Eclipse) Getting Started Guide 34 Work with SQL Tables case, but the latency transferring each row must be factored into this calculation. The Data Hub will communicate filter criteria to the remote database wherever possible to minimize the size of the result set at the data source. This will not help in cases where filtering is not specified in the SQL, or the volume of data is significant in terms of how long it takes to transfer over the Internet. Indexing The work unit of the cloud world is the map-reduce job, which typically processes an entire file or table each time it runs. The map-reduce paradigm relies upon the data being spread over a large number of nodes to achieve massively parallel processing. Cloud databases are still very rudimentary in terms of indexing non-identifier fields because they are not required in the map-reduce world. Toad for Cloud Databases uses some strategies to improve the performance of unindexed queries, but there is no performance substitute for indexes in the source data. Amazon SimpleDB has the best support. It provides a singleton index on every attribute in a domain. Compound filter conditions are supported, but a sub-query is executed for each attribute in the filter and all result sets are subsequently joined according to the logic of the filter. Obviously this strategy is not efficient at dealing with large data volumes where the filtered attribute has relatively few values. Examples of such attributes are gender or state. Using count(*) Queries This is an area where cloud or NoSQL databases conceptually differ from RDBMS. A count(*) query is fairly common in the relational world but quite inappropriate in the cloud world for two reasons. The first is the nature of the problem that cloud databases resolve, specifically the large volumes of data involved in a typical cloud database operation. If counting the rows of a table with five million rows can be slow in an RDBMS, then imagine the cost of counting tens or hundreds of millions in a cloud database. The second reason pertains to implementation of cloud databases. A count(*) query against a large table in the relational world is usually qualified by a where clause filter that is supported by an index. The index allows the query to execute efficiently despite the large amount of data. Lack of index support in cloud databases can mean the equivalent of a full table scan just to count a handful of rows. Combined with the large data volumes inherent in cloud computing, counting rows is not something that should be treated as a trivial operation. Using Views for ODBC Performance One way to solve the problem of high network overheads is to use views when connecting to a relational database through ODBC. Consider the following query that finds the number of customers serviced by each sales rep: select e.employee_id, e.name, count(*) from customer c Toad for Cloud Databases (Eclipse) Getting Started Guide 35 Work with SQL Tables inner join employee e on e.employee_id = c.sales_rep_id group by e.employee_id, e.name This query would be perfectly fine in a relational database, provided there was an index on the employee's ID column. The execution plan of this query would be to do a full table scan of the customer table and join each row to the employee table. This query took around half a second when run directly in a SQL Server 2008 database with 5,000 customers spread over 20 sales reps. Now reconsider this query where that same SQL Server database is mapped in Toad using an ODBC connection. In this case, the Data Hub will need to copy every row from the customer table over the network from the data source, and then make a separate single ODBC fetch from the employee table for each customer row. This latter set-up took around 10 seconds, which will amount to serious time with a large number of rows. The solution is to create a view in the source database and map that as a table in Toad. The view performs the query in the source relational database without copying the rows across the network. The next release of Toad will provide a SQL pass through mechanism whereby in-line views i.e. an inner SELECT statement embedded in the FROM clause of the outer SELECT statement will be passed straight through to the target data source. This will remove the need to create such a view temporarily for an ad-hoc SQL statement. ODBC Table: Error on UPDATE or DELETE with WHERE With certain ODBC drivers on an ODBC table, executing an SQL UPDATE or DELETE statement with a WHERE clause may return an error. The drivers are attempting to perform the statement while a SELECT cursor is still open on the same connection. Oracle ODBC: Table Cannot be Queried. Table or View Does not Exist Connect directly to the schemas which contain the tables you want to work with or create a synonym for the tables in the schema to which you connect. Even if you see tables in other schemas, you may have problems working with them. Appendix: Contact Quest Contact Quest Support Quest Support is available to customers who have a trial version of a Quest product or who have purchased a Quest product and have a valid maintenance contract. Quest Support provides unlimited 24x7 access to SupportLink, our self-service portal. Visit SupportLink at http://support.quest.com. From SupportLink, you can do the following: l Retrieve thousands of solutions from our online Knowledgebase l Download the latest releases and service packs l Create, update and review Support cases View the Global Support Guide for a detailed explanation of support programs, online services, contact information, policies and procedures. The guide is available at: http://support.quest.com. Toad Community Get the latest product information, find helpful resources, and join a discussion with the Toad for Cloud Databases team and other community members. Join the Toad for Cloud Databases community at http://toadforcloud.com/index.jspa. Contact Quest Software Email [email protected] Quest Software, Inc. World Headquarters Mail 5 Polaris Way Aliso Viejo, CA 92656 USA Web site www.quest.com See our web site for regional and international office information. About Quest Software Quest Software simplifies and reduces the cost of managing IT for more than 100,000 customers worldwide. Our innovative solutions make solving the toughest IT management problems easier, Toad for Cloud Databases (Eclipse) Getting Started Guide 37 Contact Quest Support enabling customers to save time and money across physical, virtual and cloud environments. For more information about Quest go to www.quest.com. map 19 Index E Eclipse A Azure Table Services 7, 18-19, 22 C Cassandra 7, 18-19, 22 cloud databases 6 column families 22 contact support 36 count(*) 34 install Toad 9 perspective 15 requirements 9 uninstall Toad 9 G Generic ODBC 18 H Hadoop HBase D data hub 6 6 7, 19 HBASE 18-19, 22 Hive 8, 19 connect 16 create 16 Connect 23 data map 17 HTTP 1.1 23 disconnect 16 install I indexing Fedora 11 Windows 10 O ODBC proxy server Fedora 13 Windows 10 uninstall Fedora 12 Windows 11 8, 19, 23 drivers Fedora 12 Windows 11 ODBC databases 18 Oracle data source cloud 34 23, 35 S 23 SimpleDB 7, 18-19, 23 Toad for Cloud Databases (Eclipse) Getting Started Guide Index SQL editor 26 inner join 28 SQL tables 33 SQL tables copy 28 SQL views 25 SQLServer 19 T Toad Explorer 26 39