keywords 'STORED AS' and 'STORED BY' cannot appear at the sa

Error message

keywords 'STORED AS' and 'STORED BY' cannot appear at the same time

What it means

genIndexDDL (used for Hive index/table DDL generation) accepts storage options storedAs (STORED AS <format>) and storedBy (STORED BY '<class>'). Hive only permits one of these clauses, so when both are non-empty the generator refuses to emit invalid DDL and returns this error.

Source

Thrown at backend/plugin/db/hive/dump.go:237

	if opts.idxProperties != nil {
		_, _ = builder.WriteString("IDXPROPERTIES ")
		formatKVPair(&builder, opts.tableProperties)
	}

	// index table.
	if opts.indexTableName != "" {
		_, _ = fmt.Fprintf(&builder, "IN TABLE %s\n", opts.indexTableName)
	}

	// row format.
	if opts.rowFmt != "" {
		_, _ = builder.WriteString(opts.rowFmt)
		_, _ = builder.WriteRune('\n')
	}

	// stored as or stored by.
	if opts.storedAs != "" && opts.storedBy != "" {
		return "", errors.New("keywords 'STORED AS' and 'STORED BY' cannot appear at the same time")
	}
	if opts.storedAs != "" {
		_, _ = fmt.Fprintf(&builder, "STORED AS %s\n", opts.storedAs)
	}
	if opts.storedBy != "" {
		_, _ = fmt.Fprintf(&builder, "STORED BY '%s'\n", opts.storedBy)
	}

	// location.
	if opts.location != "" {
		_, _ = builder.WriteString(opts.location)
		_, _ = builder.WriteRune('\n')
	}

	// table properties.
	if opts.tableProperties != nil {
		_, _ = builder.WriteString("TBLPROPERTIES (")
		formatKVPair(&builder, opts.tableProperties)

View on GitHub (pinned to 1870550677)

Solutions

Fix the source metadata so the object declares only one storage clause — a storage-handler table (STORED BY) should not also set a file format (STORED AS).
If both values come from upstream sync code, correct the field mapping so storedBy takes precedence and storedAs is left empty (or vice versa).
As a workaround, drop and recreate the Hive object with only one storage specification, then re-run the dump.

Example fix

// before
opts := &IndexDDLOptions{storedAs: "PARQUET", storedBy: "org.apache.hadoop.hive.hbase.HBaseStorageHandler"}
// after
opts := &IndexDDLOptions{storedBy: "org.apache.hadoop.hive.hbase.HBaseStorageHandler"} // drop storedAs when storedBy is set

Defensive patterns

Strategy: validation

Validate before calling

func validateStorage(opts *IndexDDLOptions) error {
  if opts.storedAs != "" && opts.storedBy != "" {
    return errors.New("STORED AS and STORED BY are mutually exclusive")
  }
  return nil
}

Try / catch

if err := genIndexDDL(opts); err != nil {
  if strings.Contains(err.Error(), "STORED AS' and 'STORED BY") {
    // fall back to the storage-handler form
    opts.storedAs = ""
    return genIndexDDL(opts)
  }
  return err
}

Prevention

Normalize metadata so an object with a storage handler (STORED BY) never also carries a file format (STORED AS).
Validate mutually exclusive DDL options at metadata sync time, not at dump time.
Document which storage clause the source object uses and map only that field during dialect translation.

When it happens

Trigger: Dump reaching genIndexDDL with IndexDDLOptions where both storedAs and storedBy were populated — e.g. metadata that merged storage info from two sources or a bug feeding the same index both a file format and a storage handler.

Common situations: Index tables created with a custom storage handler (STORED BY) whose metadata also carries a STORED AS format; manually patched schema metadata; dialect mappings translating both fields from a foreign dialect.

Related errors

AI-assisted analysis of bytebase/bytebase@1870550677 (2026-09-06). Data as JSON: /api/errors/7b5419cb6d0895b5. Report an issue: GitHub.