Skip to content
This repository has been archived by the owner on Feb 12, 2023. It is now read-only.

Improve compatibility with csvsql #1

Open
iainelder opened this issue Oct 29, 2021 · 0 comments
Open

Improve compatibility with csvsql #1

iainelder opened this issue Oct 29, 2021 · 0 comments

Comments

@iainelder
Copy link
Owner

Using aws-org-inventory version 0.2.0.

cases.csv was created using this command:

poetry run aws-org-inventory support describe_cases cases > cases.csv
$ csvsql --snifflimit 0 --query 'SELECT 1 FROM cases' -- cases.csv
/home/isme/.local/pipx/venvs/csvkit/lib/python3.8/site-packages/agate/utils.py:276: UnnamedColumnWarning: Column 0 has no name. Using "a".
OperationalError: (sqlite3.OperationalError) duplicate column name: status
[SQL: 
CREATE TABLE cases (
	a FLOAT, 
	"Id" FLOAT, 
	"Arn" VARCHAR, 
	"Email" VARCHAR, 
	"Name" VARCHAR, 
	"Status" VARCHAR, 
	"AssumeRoleSuccess" BOOLEAN, 
	"caseId" VARCHAR, 
	"displayId" FLOAT, 
	subject VARCHAR, 
	status VARCHAR, 
	"serviceCode" VARCHAR, 
	"categoryCode" VARCHAR, 
	"severityCode" VARCHAR, 
	"submittedBy" VARCHAR, 
	"timeCreated" TIMESTAMP, 
	"recentCommunications" VARCHAR, 
	"ccEmailAddresses" VARCHAR, 
	language VARCHAR, 
	CHECK ("AssumeRoleSuccess" IN (0, 1))
)

]
(Background on this error at: http://sqlalche.me/e/13/e3q8)

Several issues to fix here (maybe split them out):

  • Avoid duplicate column names by removing less-useful columns added by botocove (Arn, Email, Name, Status) so that only Id remains
  • Rename Id column to InventoryAccountId (not just AccountId because it may still clash with stack set APIs)
  • Remove or replace the unnamed index column (this forced me to use snifflimit)
    • replace with AccountId and Id of resource (may be hard to determine resource Id in the general case)
    • can you have a data frame without an index, or at least hide it on export?
    • replace with a monotonically increasing id
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant