Database Types - Detailed Connection Guides

This guide provides detailed instructions for connecting to each supported database type, including where to find credentials and specific configuration requirements.

MySQL & MariaDB

MySQL and MariaDB are popular open-source relational database management systems. They share the same connection parameters.

Connection Parameters

• Host: The IP address or hostname of your MySQL server
• Port: Default is 3306, but may vary
• Database Name: The specific database you want to connect to
• Username: Your MySQL username
• Password: Your MySQL password

Where to Find Credentials

• Self-hosted: Check your MySQL configuration files or contact your system administrator
• cPanel/Shared hosting: Available in your hosting control panel under "MySQL Databases"
• AWS RDS: Available in the AWS RDS console
• Google Cloud SQL: Available in the Google Cloud Console
• Azure Database: Available in the Azure portal

Common Connection Issues

• Ensure your database allows external connections
• Check firewall settings on port 3306
• Verify user has permission to connect remotely
• For cloud services, ensure your IP is whitelisted

Example Configuration

Host: mysql.example.com
Port: 3306
Database: my_database
Username: db_user
Password: secure_password

PostgreSQL

PostgreSQL is a powerful open-source object-relational database system.

Connection Parameters

• Host: The IP address or hostname of your PostgreSQL server
• Port: Default is 5432
• Database Name: The specific database you want to connect to
• Username: Your PostgreSQL username
• Password: Your PostgreSQL password

Where to Find Credentials

• Self-hosted: Check pg_hba.conf and postgresql.conf files
• Heroku Postgres: Available in your app's Settings → Config Vars
• AWS RDS: Available in the AWS RDS console
• Google Cloud SQL: Available in the Google Cloud Console
• Azure Database: Available in the Azure portal
• DigitalOcean: Available in your DigitalOcean control panel

Common Connection Issues

• Check pg_hba.conf for allowed connections
• Verify postgresql.conf allows connections on the correct interface
• Ensure user has CONNECT privilege on the database
• Check firewall settings on port 5432

Example Configuration

Host: postgres.example.com
Port: 5432
Database: my_database
Username: postgres_user
Password: secure_password

Google BigQuery

BigQuery is Google's fully-managed data warehouse for analytics.

Connection Requirements

• Google Cloud Project with BigQuery API enabled
• Service Account with appropriate permissions
• Service Account JSON key file

Setting Up Access

1. Create a Google Cloud Project:

   • Go to Google Cloud Console
   • Create a new project or select existing one

2. Enable BigQuery API:

   • Navigate to APIs & Services → Library
   • Search for "BigQuery API"
   • Click "Enable"

3. Create Service Account:

   • Go to IAM & Admin → Service Accounts
   • Click "Create Service Account"
   • Provide name and description

4. Assign Permissions:

   • BigQuery Data Viewer (minimum)
   • BigQuery Job User (for running queries)
   • Additional roles as needed for your data

5. Generate Key File:

   • Click on your service account
   • Go to Keys tab → Add Key → Create New Key
   • Choose JSON format and download

Connection Parameters

• Project ID: Your Google Cloud Project ID
• Service Account JSON: Contents of the downloaded JSON key file
• Dataset (optional): Default dataset to use

Example Configuration

Project ID: my-bigquery-project-123456
Service Account JSON: {
  "type": "service_account",
  "project_id": "my-bigquery-project-123456",
  ...
}

Amazon Redshift

Redshift is Amazon's cloud-based data warehouse service.

Connection Parameters

• Host: Your Redshift cluster endpoint
• Port: Default is 5439
• Database Name: The database name in your cluster
• Username: Your Redshift username
• Password: Your Redshift password

Where to Find Credentials

1. AWS Console:

   • Navigate to Amazon Redshift
   • Select your cluster
   • Connection details shown in cluster properties

2. Endpoint Format: cluster-name.identifier.region.redshift.amazonaws.com

Setting Up Access

• Ensure cluster is publicly accessible (if connecting from outside AWS)
• Configure security groups to allow inbound connections
• Create database users with appropriate permissions

Common Connection Issues

• Check security group inbound rules for port 5439
• Verify cluster is in "available" state
• Ensure user has CONNECT privilege on database
• Check VPC and subnet configurations

Example Configuration

Host: my-cluster.abc123def456.us-west-2.redshift.amazonaws.com
Port: 5439
Database: analytics
Username: redshift_user
Password: secure_password

Azure SQL Database

Azure SQL Database is Microsoft's cloud-based SQL database service.

Connection Parameters

• Server: Your Azure SQL server name
• Database Name: The specific database name
• Username: Your SQL authentication username
• Password: Your SQL authentication password

Where to Find Credentials

1. Azure Portal:

   • Navigate to SQL databases
   • Select your database
   • Click "Connection strings" for full details

2. Server Format: server-name.database.windows.net

Authentication Options

• SQL Authentication: Username/password (recommended for applications)
• Azure AD Authentication: Uses Azure Active Directory (for user access)

Setting Up Access

• Configure firewall rules to allow your IP address
• Ensure SQL Authentication is enabled
• Create database users with appropriate permissions

Common Connection Issues

• Check Azure SQL firewall settings
• Verify SQL Authentication is enabled
• Ensure user has db_datareader permission minimum
• Check connection string format

Example Configuration

Host: myserver.database.windows.net
Port: 1433
Database: mydatabase
Username: sqladmin
Password: secure_password

Google Sheets

Google Sheets integration allows you to connect to spreadsheets in your Google Drive as data sources.

Connection Requirements

• Google account with access to Google Sheets
• OAuth 2.0 authentication through Google
• Google Sheets and Google Drive API access

Setting Up Access

1. Authentication Process:

   • Click "Connect with Google" when creating the connection
   • Sign in to your Google account
   • Grant permission to access your Google Sheets

2. Permissions Required:

   • Read access to your Google Sheets
   • Read access to your Google Drive (to list spreadsheets)

How It Works

• Spreadsheets as Data Sources: Each spreadsheet in your Google Drive becomes available as a data source
• Sheets as Data Tables: Each sheet within a spreadsheet provides raw data
• First Row as Headers: The first row of each sheet is treated as column headers
• Data Types: All data is imported as text by default

Data Access Method

Unlike traditional databases, Google Sheets doesn't support SQL queries. Instead:

• Raw Data Import: PyQueryHub imports the complete sheet data
• Chart-Level Filtering: All filtering, sorting, and aggregation happens in the chart configuration
• No SQL Support: You cannot write SELECT statements or WHERE clauses
• Sheet Selection: Choose which spreadsheet and sheet to use as your data source

Supported Operations

• Read-only access: You can only read data, not modify it
• Real-time data: Data is fetched directly from Google Sheets when reports run
• Automatic schema detection: Column names detected from first row
• Chart-based filtering: Use chart controls to filter and aggregate data

Limitations

• No SQL querying: Cannot use WHERE, GROUP BY, JOIN, or other SQL operations
• Chart-level processing only: All data manipulation happens in chart configuration
• Read-only access: No INSERT/UPDATE/DELETE operations
• Limited to your Google account: Only spreadsheets you have access to
• Performance: Depends on spreadsheet size and Google API limits
• Data types: All imported as text (formatting applied in charts)

Best Practices

• Keep column headers in the first row
• Use consistent data formatting within columns
• Avoid special characters in sheet names
• Consider data size - large sheets may impact performance
• Structure data appropriately since you can't use SQL to reshape it
• Use multiple sheets for different data entities rather than complex queries

Workflow Example

1. Data Source: Choose "Sales Data 2024" spreadsheet
2. Sheet Selection: Select "Q1 Results" sheet
3. Chart Creation: Create charts that filter/aggregate the raw sheet data
4. No SQL: All processing happens through chart configuration, not queries

Troubleshooting

• Authentication expired: Re-authenticate with Google
• Spreadsheet not found: Check spelling and ensure it exists in your Drive
• Permission denied: Verify you have access to the spreadsheet
• Empty results: Check if the sheet has data and proper headers

SSH Tunneling (All Database Types)

For enhanced security, you can connect through an SSH tunnel. This is available for all traditional database types (MySQL, PostgreSQL, Redshift, Azure SQL).

When to Use SSH Tunneling

• Database server is only accessible through a jump/bastion server
• Additional security layer required
• Database is behind a firewall that allows SSH but not direct database connections

SSH Connection Parameters

• SSH Host: The hostname or IP of your SSH server
• SSH Port: Usually 22 (default SSH port)
• SSH Username: Your SSH username
• SSH Authentication: Either password or private key
• SSH Password: Your SSH password (if not using key)
• SSH Private Key: Contents of your private key file (if not using password)

SSH Key Setup

1. Generate SSH key pair (if you don't have one):

   ssh-keygen -t rsa -b 4096

2. Copy public key to server:

   ssh-copy-id username@ssh-server.com

3. Use private key contents in PyQueryHub:

   • Copy the entire contents of your private key file
   • Paste into the "SSH Private Key" field

SSL/TLS Encryption

SSL/TLS encryption secures the connection between PyQueryHub and your database. Available for MySQL, PostgreSQL, and other traditional databases.

SSL Certificate Types

• CA Certificate: Certificate Authority certificate
• Client Certificate: Your client certificate (for mutual authentication)
• Client Key: Your client private key

Where to Get SSL Certificates

• Cloud providers: Usually provide downloadable certificates
• Self-signed: Generated by your database administrator
• Certificate authorities: For production environments

SSL Configuration

1. Obtain certificates from your database provider
2. Copy certificate contents (not file paths)
3. Paste into appropriate fields in PyQueryHub
4. Test connection to verify SSL is working

Common SSL Issues

• Certificate format problems (ensure PEM format)
• Expired certificates
• Hostname mismatch in certificates
• Missing intermediate certificates

General Troubleshooting

Connection Test Failures

1. Verify credentials: Double-check all connection parameters
2. Network connectivity: Ensure the database server is reachable
3. Firewall rules: Check both local and server-side firewalls
4. User permissions: Verify database user has necessary privileges
5. Service status: Confirm the database service is running

Performance Issues

1. Network latency: Consider geographic proximity to database
2. Database load: Check if database server is under heavy load
3. Query optimization: Review query efficiency
4. Connection pooling: Database server connection limits

Security Best Practices

1. Use strong passwords: Complex, unique passwords for database users
2. Limit permissions: Grant minimum necessary database privileges
3. Enable encryption: Use SSL/TLS when available
4. Regular audits: Review and rotate credentials periodically
5. Network security: Use SSH tunneling when appropriate

Getting Help

If you continue to experience connection issues:

1. Check database server logs for error details
2. Verify network connectivity using tools like telnet or nc
3. Test connection from another client to isolate issues
4. Contact our support team with specific error messages

For database-specific issues, consult your database provider's documentation or support resources.