0
# Configuration and Utilities
1

2
Phoenix provides extensive configuration options and utility classes for customizing behavior, optimizing performance, and handling common operations. The configuration system integrates with Hadoop configuration while providing Phoenix-specific settings.
3

4
## Core Imports
5

6
```java
7
import org.apache.phoenix.query.QueryServicesOptions;
8
import org.apache.phoenix.schema.ConnectionProperty;
9
import org.apache.phoenix.util.*;
10
import org.apache.hadoop.conf.Configuration;
11
import java.util.Properties;
12
```
13

14
## Configuration Management
15

16
### QueryServicesOptions
17

18
Default configuration options for Phoenix query services with comprehensive tuning parameters.
19

20
```java{ .api }
21
public class QueryServicesOptions {
22
    // Connection settings
23
    public static final int DEFAULT_KEEP_ALIVE_MS = 60000;
24
    public static final int DEFAULT_THREAD_POOL_SIZE = 128;
25
    public static final boolean DEFAULT_QUEUE_SIZE_BIG = false;
26

27
    // Query execution settings
28
    public static final int DEFAULT_QUERY_TIMEOUT_MS = 600000;  // 10 minutes
29
    public static final int DEFAULT_THREAD_TIMEOUT_MS = 600000;
30
    public static final int DEFAULT_SPOOL_THRESHOLD_BYTES = 20971520;  // 20MB
31

32
    // Scan settings
33
    public static final int DEFAULT_SCAN_CACHE_SIZE = 1000;
34
    public static final int DEFAULT_MAX_SERVER_CACHE_SIZE = 104857600;  // 100MB
35
    public static final long DEFAULT_MAX_SERVER_CACHE_TIME_TO_LIVE_MS = 30000;
36

37
    // Index settings
38
    public static final boolean DEFAULT_INDEX_ASYNC_BUILD_ENABLED = true;
39
    public static final int DEFAULT_INDEX_HANDLER_COUNT = 30;
40

41
    // Statistics settings
42
    public static final boolean DEFAULT_STATS_GUIDEPOST_WIDTH_BYTES = 104857600;  // 100MB
43
    public static final boolean DEFAULT_STATS_UPDATE_FREQ_MS = 900000;  // 15 minutes
44

45
    // Get configuration values
46
    public static boolean getBoolean(String name, boolean defaultValue)
47
    public static int getInt(String name, int defaultValue)
48
    public static long getLong(String name, long defaultValue)
49
    public static String getString(String name, String defaultValue)
50
}
51
```
52

53
### ConnectionProperty
54

55
Enumeration of Phoenix connection properties with type information and validation.
56

57
```java{ .api }
58
public enum ConnectionProperty {
59
    // Core connection properties
60
    PHOENIX_QUERY_TIMEOUT_MS("phoenix.query.timeoutMs", "Query timeout in milliseconds",
61
                             DataType.INTEGER, "600000", false),
62
    PHOENIX_QUERY_DATE_FORMAT_TIMEZONE("phoenix.query.dateFormatTimeZone",
63
                                     "Timezone for date formatting", DataType.STRING,
64
                                     QueryServicesOptions.DEFAULT_DATE_FORMAT_TIMEZONE, false),
65

66
    // Performance tuning
67
    SCAN_CACHE_SIZE("ScanCacheSize", "Number of rows to cache for scans",
68
                   DataType.INTEGER, "1000", false),
69
    AUTO_COMMIT("AutoCommit", "Auto commit transactions",
70
               DataType.BOOLEAN, "false", false),
71

72
    // Security and authentication
73
    AUTHENTICATION("Authentication", "Authentication method",
74
                  DataType.STRING, null, false),
75
    KERBEROS_PRINCIPAL("KerberosPrincipal", "Kerberos principal",
76
                      DataType.STRING, null, false),
77

78
    // Transaction settings
79
    TRANSACTIONAL("Transactional", "Enable transactional behavior",
80
                 DataType.BOOLEAN, "false", false),
81
    TRANSACTION_TIMEOUT("TransactionTimeout", "Transaction timeout in seconds",
82
                       DataType.INTEGER, "300", false);
83

84
    public String getPropertyName()
85
    public String getDescription()
86
    public DataType getDataType()
87
    public String getDefaultValue()
88
    public boolean isRequired()
89
    public Object getValue(String stringValue)
90
    public String toString(Object value)
91
}
92
```
93

94
**Usage:**
95
```java
96
// Set connection properties
97
Properties props = new Properties();
98
props.setProperty(ConnectionProperty.PHOENIX_QUERY_TIMEOUT_MS.getPropertyName(), "300000");
99
props.setProperty(ConnectionProperty.SCAN_CACHE_SIZE.getPropertyName(), "2000");
100
props.setProperty(ConnectionProperty.AUTO_COMMIT.getPropertyName(), "false");
101

102
// Create connection with properties
103
String url = "jdbc:phoenix:localhost:2181";
104
Connection connection = DriverManager.getConnection(url, props);
105

106
// Get property values from connection
107
PhoenixConnection phoenixConn = connection.unwrap(PhoenixConnection.class);
108
String timeoutValue = phoenixConn.getClientInfo(
109
    ConnectionProperty.PHOENIX_QUERY_TIMEOUT_MS.getPropertyName()
110
);
111
String cacheSize = phoenixConn.getClientInfo(
112
    ConnectionProperty.SCAN_CACHE_SIZE.getPropertyName()
113
);
114

115
System.out.println("Query timeout: " + timeoutValue + "ms");
116
System.out.println("Scan cache size: " + cacheSize);
117

118
// Validate property values
119
for (ConnectionProperty prop : ConnectionProperty.values()) {
120
    String value = props.getProperty(prop.getPropertyName());
121
    if (value != null) {
122
        try {
123
            Object validatedValue = prop.getValue(value);
124
            System.out.println(prop.getPropertyName() + " = " + validatedValue);
125
        } catch (IllegalArgumentException e) {
126
            System.err.println("Invalid value for " + prop.getPropertyName() + ": " + value);
127
        }
128
    }
129
}
130
```
131

132
## Utility Classes
133

134
### ByteUtil
135

136
Utility methods for byte array operations optimized for Phoenix/HBase usage.
137

138
```java{ .api }
139
public class ByteUtil {
140
    // Byte array concatenation
141
    public static byte[] concat(byte[]... arrays)
142
    public static byte[] concat(byte[] first, byte[] second)
143

144
    // Byte array comparison
145
    public static int compare(byte[] left, byte[] right)
146
    public static boolean equals(byte[] left, byte[] right)
147

148
    // Byte array utilities
149
    public static byte[] copyKeyBytesIfNecessary(ImmutableBytesWritable ptr)
150
    public static int getVarCharLength(byte[] bytes, int offset, int length)
151
    public static int calculateHashCode(ImmutableBytesWritable ptr)
152

153
    // Constants
154
    public static final byte[] EMPTY_BYTE_ARRAY = new byte[0];
155
    public static final ImmutableBytesWritable EMPTY_BYTE_ARRAY_PTR =
156
        new ImmutableBytesWritable(EMPTY_BYTE_ARRAY);
157
}
158
```
159

160
### DateUtil
161

162
Utility methods for date/time operations with timezone and format handling.
163

164
```java{ .api }
165
public class DateUtil {
166
    // Date formatting
167
    public static String DEFAULT_DATE_FORMAT = "yyyy-MM-dd HH:mm:ss";
168
    public static String DEFAULT_MS_DATE_FORMAT = "yyyy-MM-dd HH:mm:ss.SSS";
169

170
    // Date parsing and formatting
171
    public static Date parseDate(String dateString)
172
    public static Date parseDate(String dateString, String formatString)
173
    public static String formatDate(Date date)
174
    public static String formatDate(Date date, String formatString)
175

176
    // Timezone handling
177
    public static Date parseDate(String dateString, String formatString, TimeZone timeZone)
178
    public static String formatDate(Date date, String formatString, TimeZone timeZone)
179

180
    // Date arithmetic
181
    public static Date addDays(Date date, int days)
182
    public static Date addMonths(Date date, int months)
183
    public static Date addYears(Date date, int years)
184

185
    // Date comparison utilities
186
    public static boolean isSameDay(Date date1, Date date2)
187
    public static long daysBetween(Date startDate, Date endDate)
188
}
189
```
190

191
### SchemaUtil
192

193
Utility methods for schema operations including name handling and DDL generation.
194

195
```java{ .api }
196
public class SchemaUtil {
197
    // Name formatting
198
    public static String getTableName(String schemaName, String tableName)
199
    public static String getColumnName(String familyName, String columnName)
200
    public static String getEscapedTableName(String schemaName, String tableName)
201

202
    // Name parsing
203
    public static String getSchemaNameFromFullName(String tableName)
204
    public static String getTableNameFromFullName(String fullTableName)
205

206
    // DDL utilities
207
    public static String getCreateTableStatement(PTable table)
208
    public static String getDropTableStatement(PName tableName)
209
    public static String getCreateIndexStatement(PTable index)
210

211
    // Schema validation
212
    public static void validateIdentifier(String identifier)
213
    public static boolean isValidIdentifier(String identifier)
214

215
    // Column utilities
216
    public static String getColumnDisplayName(byte[] cf, byte[] cq)
217
    public static byte[] getEmptyColumnFamily(PName defaultColumnFamily)
218
}
219
```
220

221
### QueryUtil
222

223
Utility methods for query operations and SQL generation.
224

225
```java{ .api }
226
public class QueryUtil {
227
    // Query construction
228
    public static String constructSelectStatement(PTable table, List<PColumn> columns)
229
    public static String constructUpsertStatement(PTable table, List<PColumn> columns)
230
    public static String constructDeleteStatement(PTable table)
231

232
    // Parameter handling
233
    public static String bindParams(String query, List<Object> parameters)
234
    public static PreparedStatement setParameters(PreparedStatement stmt, List<Object> params)
235

236
    // Query analysis
237
    public static boolean isSelect(String sql)
238
    public static boolean isUpsert(String sql)
239
    public static boolean isDDL(String sql)
240

241
    // Result processing
242
    public static List<String> getColumnNames(ResultSetMetaData metaData)
243
    public static List<Object> getRowValues(ResultSet rs, ResultSetMetaData metaData)
244
}
245
```
246

247
### ClientUtil
248

249
Client-side utility methods for connection management and error handling.
250

251
```java{ .api }
252
public class ClientUtil {
253
    // Connection utilities
254
    public static PhoenixConnection getPhoenixConnection(Connection conn) throws SQLException
255
    public static boolean isConnectionClosed(Connection conn)
256
    public static void closeQuietly(Connection conn)
257
    public static void closeQuietly(Statement stmt)
258
    public static void closeQuietly(ResultSet rs)
259

260
    // Error handling
261
    public static SQLException parseServerException(SQLException e)
262
    public static boolean isRetryableException(SQLException e)
263
    public static void handleRetryableException(SQLException e, int maxRetries)
264

265
    // Performance utilities
266
    public static void enableQueryLogging(Connection conn)
267
    public static void disableQueryLogging(Connection conn)
268
    public static QueryMetrics getQueryMetrics(Statement stmt)
269
}
270
```
271

272
**Usage:**
273
```java
274
// ByteUtil operations
275
byte[] array1 = Bytes.toBytes("Hello");
276
byte[] array2 = Bytes.toBytes(" World");
277
byte[] combined = ByteUtil.concat(array1, array2);
278
String result = Bytes.toString(combined);  // "Hello World"
279

280
boolean areEqual = ByteUtil.equals(array1, array2);
281
int comparison = ByteUtil.compare(array1, array2);
282

283
// DateUtil operations
284
String dateString = "2023-12-25 14:30:00";
285
Date parsedDate = DateUtil.parseDate(dateString);
286
String formattedDate = DateUtil.formatDate(parsedDate, DateUtil.DEFAULT_MS_DATE_FORMAT);
287

288
// Add time periods
289
Date futureDate = DateUtil.addDays(parsedDate, 30);
290
Date nextMonth = DateUtil.addMonths(parsedDate, 1);
291
long daysDiff = DateUtil.daysBetween(parsedDate, futureDate);
292

293
System.out.println("Original date: " + formattedDate);
294
System.out.println("30 days later: " + DateUtil.formatDate(futureDate));
295
System.out.println("Days between: " + daysDiff);
296

297
// SchemaUtil operations
298
String fullTableName = SchemaUtil.getTableName("myschema", "users");
299
String escapedName = SchemaUtil.getEscapedTableName("my schema", "user table");
300
String schemaName = SchemaUtil.getSchemaNameFromFullName("myschema.users");
301

302
System.out.println("Full table name: " + fullTableName);
303
System.out.println("Escaped name: " + escapedName);
304
System.out.println("Schema name: " + schemaName);
305

306
// Validate identifiers
307
boolean isValid = SchemaUtil.isValidIdentifier("valid_name");
308
try {
309
    SchemaUtil.validateIdentifier("invalid-name!");
310
} catch (IllegalArgumentException e) {
311
    System.err.println("Invalid identifier: " + e.getMessage());
312
}
313

314
// QueryUtil operations
315
PTable table = connection.getTable(PNameFactory.newName("users"));
316
List<PColumn> columns = table.getColumns();
317

318
String selectQuery = QueryUtil.constructSelectStatement(table, columns);
319
String upsertQuery = QueryUtil.constructUpsertStatement(table, columns);
320

321
System.out.println("Generated SELECT: " + selectQuery);
322
System.out.println("Generated UPSERT: " + upsertQuery);
323

324
// ClientUtil operations
325
PhoenixConnection phoenixConn = ClientUtil.getPhoenixConnection(connection);
326
boolean isClosed = ClientUtil.isConnectionClosed(connection);
327

328
// Safe resource cleanup
329
Statement stmt = null;
330
ResultSet rs = null;
331
try {
332
    stmt = connection.createStatement();
333
    rs = stmt.executeQuery("SELECT COUNT(*) FROM users");
334
    // Process results...
335
} finally {
336
    ClientUtil.closeQuietly(rs);
337
    ClientUtil.closeQuietly(stmt);
338
}
339
```
340

341
## Data Loading Utilities
342

343
### CSVCommonsLoader
344

345
Utility for loading CSV data into Phoenix tables with flexible configuration options.
346

347
```java{ .api }
348
public class CSVCommonsLoader {
349
    // Constructor
350
    public CSVCommonsLoader(PhoenixConnection conn, String tableName,
351
                           List<ColumnInfo> columns, boolean strict)
352

353
    // CSV loading
354
    public void upsert(String csvValue) throws SQLException
355
    public void upsert(InputStream inputStream) throws SQLException
356
    public void upsert(Reader reader) throws SQLException
357

358
    // Configuration
359
    public void setDelimiter(char delimiter)
360
    public void setQuoteChar(char quoteChar)
361
    public void setEscapeChar(char escapeChar)
362
    public void setArrayElementSeparator(String separator)
363

364
    // Statistics
365
    public long getUpsertCount()
366
    public long getErrorCount()
367
}
368
```
369

370
### ColumnInfo
371

372
Information about columns for data loading operations with type and format specifications.
373

374
```java{ .api }
375
public class ColumnInfo {
376
    // Constructor
377
    public ColumnInfo(String columnName, int sqlType)
378
    public ColumnInfo(String columnName, int sqlType, String columnDisplayName)
379

380
    // Column properties
381
    public String getColumnName()
382
    public int getSqlType()
383
    public String getColumnDisplayName()
384
    public PDataType getDataType()
385

386
    // Type-specific settings
387
    public Integer getPrecision()
388
    public Integer getScale()
389
    public void setPrecision(Integer precision)
390
    public void setScale(Integer scale)
391

392
    // Array handling
393
    public boolean isArrayType()
394
    public String getArrayElementSeparator()
395
    public void setArrayElementSeparator(String separator)
396
}
397
```
398

399
**Usage:**
400
```java
401
// Set up column information for CSV loading
402
List<ColumnInfo> columns = Arrays.asList(
403
    new ColumnInfo("id", Types.BIGINT),
404
    new ColumnInfo("name", Types.VARCHAR),
405
    new ColumnInfo("email", Types.VARCHAR),
406
    new ColumnInfo("salary", Types.DECIMAL),
407
    new ColumnInfo("hire_date", Types.DATE),
408
    new ColumnInfo("skills", Types.ARRAY)  // Array column
409
);
410

411
// Configure decimal precision
412
ColumnInfo salaryColumn = columns.get(3);
413
salaryColumn.setPrecision(10);
414
salaryColumn.setScale(2);
415

416
// Configure array separator
417
ColumnInfo skillsColumn = columns.get(5);
418
skillsColumn.setArrayElementSeparator("|");
419

420
// Create CSV loader
421
PhoenixConnection phoenixConn = connection.unwrap(PhoenixConnection.class);
422
CSVCommonsLoader csvLoader = new CSVCommonsLoader(phoenixConn, "employees", columns, true);
423

424
// Configure CSV format
425
csvLoader.setDelimiter(',');
426
csvLoader.setQuoteChar('"');
427
csvLoader.setEscapeChar('\\');
428

429
// Load CSV data
430
String csvData = """
431
    1,"John Doe","john@example.com",75000.50,"2023-01-15","Java|Python|SQL"
432
    2,"Jane Smith","jane@example.com",82000.00,"2023-02-01","Python|JavaScript|React"
433
    3,"Bob Johnson","bob@example.com",68000.75,"2023-03-10","Java|Spring|Hibernate"
434
    """;
435

436
try (StringReader reader = new StringReader(csvData)) {
437
    csvLoader.upsert(reader);
438
}
439

440
// Get loading statistics
441
long upsertCount = csvLoader.getUpsertCount();
442
long errorCount = csvLoader.getErrorCount();
443

444
System.out.println("Records loaded: " + upsertCount);
445
System.out.println("Errors: " + errorCount);
446

447
// Load from file
448
try (FileInputStream fileInput = new FileInputStream("employees.csv")) {
449
    csvLoader.upsert(fileInput);
450
}
451
```
452

453
## Configuration Examples
454

455
### Connection Configuration
456

457
```java
458
// Comprehensive connection configuration
459
public Properties createPhoenixProperties() {
460
    Properties props = new Properties();
461

462
    // Query settings
463
    props.setProperty(ConnectionProperty.PHOENIX_QUERY_TIMEOUT_MS.getPropertyName(), "900000"); // 15 minutes
464
    props.setProperty(ConnectionProperty.SCAN_CACHE_SIZE.getPropertyName(), "5000");
465
    props.setProperty(ConnectionProperty.AUTO_COMMIT.getPropertyName(), "false");
466

467
    // Date/time settings
468
    props.setProperty(ConnectionProperty.PHOENIX_QUERY_DATE_FORMAT_TIMEZONE.getPropertyName(), "UTC");
469

470
    // Performance tuning
471
    props.setProperty("phoenix.query.spoolThresholdBytes", "52428800"); // 50MB
472
    props.setProperty("phoenix.query.maxGlobalMemoryPercentage", "20");
473
    props.setProperty("phoenix.query.targetConcurrency", "16");
474

475
    // Retry settings
476
    props.setProperty("phoenix.query.maxRetries", "5");
477
    props.setProperty("phoenix.query.retryInitialBackoffMs", "1000");
478

479
    // Security settings (if needed)
480
    // props.setProperty(ConnectionProperty.AUTHENTICATION.getPropertyName(), "KERBEROS");
481
    // props.setProperty(ConnectionProperty.KERBEROS_PRINCIPAL.getPropertyName(), "user@REALM");
482

483
    return props;
484
}
485

486
// Use configured connection
487
Properties props = createPhoenixProperties();
488
String url = "jdbc:phoenix:zk1,zk2,zk3:2181:/hbase";
489
Connection connection = DriverManager.getConnection(url, props);
490
```
491

492
### Performance Tuning Configuration
493

494
```java
495
// Configure Phoenix for high-performance workloads
496
public class PerformanceTuningConfig {
497
    public static void configureForBulkOperations(PhoenixConnection connection) throws SQLException {
498
        // Increase batch sizes
499
        connection.setClientInfo("phoenix.mutate.batchSize", "10000");
500
        connection.setClientInfo("phoenix.query.maxGlobalMemorySize", "2147483648"); // 2GB
501

502
        // Optimize scans
503
        connection.setClientInfo("phoenix.scan.cacheSize", "10000");
504
        connection.setClientInfo("phoenix.scan.maxResultSize", "104857600"); // 100MB
505

506
        // Disable auto-commit for batch operations
507
        connection.setAutoCommit(false);
508
    }
509

510
    public static void configureForOLTP(PhoenixConnection connection) throws SQLException {
511
        // Optimize for small, fast queries
512
        connection.setClientInfo("phoenix.scan.cacheSize", "100");
513
        connection.setClientInfo("phoenix.query.timeoutMs", "30000"); // 30 seconds
514

515
        // Enable auto-commit for single operations
516
        connection.setAutoCommit(true);
517
    }
518

519
    public static void configureForAnalytics(PhoenixConnection connection) throws SQLException {
520
        // Optimize for large analytical queries
521
        connection.setClientInfo("phoenix.query.timeoutMs", "1800000"); // 30 minutes
522
        connection.setClientInfo("phoenix.scan.cacheSize", "1000");
523
        connection.setClientInfo("phoenix.query.maxGlobalMemoryPercentage", "40");
524

525
        // Use larger spool threshold
526
        connection.setClientInfo("phoenix.query.spoolThresholdBytes", "209715200"); // 200MB
527
    }
528
}
529

530
// Apply performance configurations
531
PhoenixConnection phoenixConn = connection.unwrap(PhoenixConnection.class);
532

533
// For bulk data loading
534
PerformanceTuningConfig.configureForBulkOperations(phoenixConn);
535

536
// For OLTP workload
537
// PerformanceTuningConfig.configureForOLTP(phoenixConn);
538

539
// For analytics workload
540
// PerformanceTuningConfig.configureForAnalytics(phoenixConn);
541
```
542

543
### Utility Helper Classes
544

545
```java
546
// Helper class for common Phoenix operations
547
public class PhoenixHelper {
548
    private final PhoenixConnection connection;
549

550
    public PhoenixHelper(Connection connection) throws SQLException {
551
        this.connection = connection.unwrap(PhoenixConnection.class);
552
    }
553

554
    public void createTableIfNotExists(String tableName, String ddl) throws SQLException {
555
        try {
556
            PTable table = connection.getTable(PNameFactory.newName(tableName));
557
            System.out.println("Table " + tableName + " already exists");
558
        } catch (TableNotFoundException e) {
559
            Statement stmt = connection.createStatement();
560
            stmt.execute(ddl);
561
            System.out.println("Created table: " + tableName);
562
            ClientUtil.closeQuietly(stmt);
563
        }
564
    }
565

566
    public long getTableRowCount(String tableName) throws SQLException {
567
        String sql = "SELECT COUNT(*) FROM " + SchemaUtil.getEscapedTableName(null, tableName);
568
        try (Statement stmt = connection.createStatement();
569
             ResultSet rs = stmt.executeQuery(sql)) {
570
            if (rs.next()) {
571
                return rs.getLong(1);
572
            }
573
            return 0;
574
        }
575
    }
576

577
    public List<String> getTableNames(String schemaName) throws SQLException {
578
        DatabaseMetaData metaData = connection.getMetaData();
579
        List<String> tableNames = new ArrayList<>();
580

581
        try (ResultSet rs = metaData.getTables(null, schemaName, "%", new String[]{"TABLE"})) {
582
            while (rs.next()) {
583
                String tableName = rs.getString("TABLE_NAME");
584
                tableNames.add(tableName);
585
            }
586
        }
587

588
        return tableNames;
589
    }
590

591
    public void optimizeTable(String tableName) throws SQLException {
592
        // Update statistics for better query planning
593
        String sql = "UPDATE STATISTICS " + SchemaUtil.getEscapedTableName(null, tableName);
594
        try (Statement stmt = connection.createStatement()) {
595
            stmt.execute(sql);
596
            System.out.println("Updated statistics for table: " + tableName);
597
        }
598
    }
599
}
600

601
// Usage
602
PhoenixHelper helper = new PhoenixHelper(connection);
603

604
// Create table if it doesn't exist
605
String createTableDDL = """
606
    CREATE TABLE IF NOT EXISTS products (
607
        id BIGINT PRIMARY KEY,
608
        name VARCHAR(100),
609
        price DECIMAL(10,2),
610
        category VARCHAR(50)
611
    )
612
    """;
613
helper.createTableIfNotExists("products", createTableDDL);
614

615
// Get table information
616
long rowCount = helper.getTableRowCount("products");
617
List<String> allTables = helper.getTableNames("PUBLIC");
618

619
System.out.println("Products table has " + rowCount + " rows");
620
System.out.println("All tables: " + allTables);
621

622
// Optimize table
623
helper.optimizeTable("products");
624
```