我怎样才能建立一个前端查询红移数据库（希望使用Rails） [英] How can I build a front end for querying a Redshift database (hopefully with Rails)
问题描述
所以我有一个红移数据库有足够的表，它的感觉值得我的时间来建立一个前端，使查询比刚才输入的SQL命令稍微容易一些。
在理想情况下，我能够通过数据库连接到一个Rails应用程序（因为我有一点经验，使用Rails）来做到这一点。我不知道我怎么会连接远程数据库红移到本地Rails应用程序，虽然，或如何使红移ActiveRecord的工作。
有没有人有任何建议/资源来帮助我开始？我接受其他选项的红移数据库连接到前端，如果有pre制选择比Rails的容易。
＃应用程序/模型/ data_warehouse.rb
级数据仓库和LT;的ActiveRecord :: Base的
establish_connectionredshift_staging
#or，如果你想拥有每环境分贝
#establish_connection红移_＃{Rails.env}
结束
请注意，我们要连接上5439，而不是默认的5432，所以我指定的端口 另外，我指定一个模式，测试版，这是我们用什么为我们的不稳定集合体，你既可以有每个环境不同的数据库如上所述，或使用不同的模式，包括他们的ActiveRecord的搜索路径
＃配置/ database.yml中
redshift_staging：
适配器：PostgreSQL的
编码：UTF8
数据库：DB03
端口：5439
池：5
schema_search_path：'测试版'
用户名：admin
密码：supersecretpassword
主持人：在这里db03.myremotehost.us #your远程主机，可能是从红移管理控制台的AWS网址
###选项2，直接PG连接
级数据仓库＆LT;的ActiveRecord :: Base的
attr_accessor：康恩
高清初始化
@conn = PG.connect（
数据库：DB03，
端口：5439，
池：5，
schema_search_path：'测试版'，
用户名：管理员，
密码：supersecretpassword，
主持人：db03.myremotehost.us
）
结束
结束
[DEV]主：0＆GT;红移=数据仓库
E，[2014-07-17T11：09：17.758957＃44535]错误 - ：PG :: InsufficientPrivilege：错误：拒绝权限设置参数client_min_messages通知：设置client_min_messages TO通知
（撬）输出错误：＃＆LT;的ActiveRecord :: StatementInvalid：PG :: InsufficientPrivilege：错误：拒绝权限设置参数client_min_messages通知：设置client_min_messages TO'通知'＆GT;
更新：
我最终会与选项1，但使用该适配器现在有多种原因：
<一个href="https://github.com/fiksu/activerecord-redshift-adapter">https://github.com/fiksu/activerecord-redshift-adapter
原因1：ActiveRecord的PostgreSQL的适配器设置client_min_messages 原因2：适配器还尝试设置时区，其红移不允许（<一href="http://docs.aws.amazon.com/redshift/latest/dg/c_redshift-and-postgres-sql.html">http://docs.aws.amazon.com/redshift/latest/dg/c_redshift-and-postgres-sql.html) 原因3：即使你改变了code在ActiveRecord的前两个错误，你碰上抱怨红移使用PostgreSQL 8.0，在这一点上我转移到适配器其他错误，将重新审视和更新，如果我后来找到更好的东西。
我在我的表重命名为base_aggregate_redshift_tests（注复数）这样的ActiveRecord可以非常容易地进行连接，如果你不能在红移改变你的表名使用set_table方法，我有评论如下
#Gemfile：
创业板activerecord4红移适配器，github上：aamine / activerecord4红移适配器
选项1
＃配置/ database.yml中
redshift_staging：
适配器：红移
编码：UTF8
数据库：DB03
端口：5439
池：5
用户名：admin
密码：supersecretpassword
主持人：db03.myremotehost.us
超时：5000
＃应用程序/模型/ base_aggregates_redshift_test.rb
#Model为匹配我的表在红移，如果你愿意，你可以set_table像我有评论如下
类BaseAggregatesRedshiftTest＆LT;的ActiveRecord :: Base的
establish_connectionredshift_staging
self.table_name =beta.base_aggregates_v2
结束
使用self.table_name
控制台 - 注意它查询正确的表，所以你可以命名你的模型任何你想要
[DEV]主：0＆GT;红移= BaseAggregatesRedshiftTest.first
研发，[2014-07-17T15：31：58.678103＃43776] DEBUG - ：BaseAggregatesRedshiftTest负荷（45.6ms）选择测试版base_aggregates_v2* FROM测试版base_aggregates_v2LIMIT 1。
选项2
＃应用程序/模型/ base_aggregates_redshift_test.rb
类BaseAggregatesRedshiftTest＆LT;的ActiveRecord :: Base的
set_tablebeta.base_aggregates_v2
的ActiveRecord :: Base.establish_connection（
适配器：红移，
编码：'UTF8'，
数据库：'分期'，
端口：'5439'，
池：'5'，
用户名：管理员，
密码：supersecretpassword，
search_schema：'测试版'，
主持人：db03.myremotehost.us，
超时：'5000'
）
结束
#in控制台，第一条记录的缩写例子，现在它使用新名称为我的红移表，只是假设我已经得到了纪录base_aggregates_redshift_tests，因为我没有设置TABLE_NAME
[DEV]主：0＆GT;红移= BaseAggregatesRedshiftTest.first
研发，[2014-07-17T15：09：39.388918＃11537] DEBUG - ：BaseAggregatesRedshiftTest负荷（45.3ms）选择base_aggregates_redshift_tests* FROMbase_aggregates_redshift_testsLIMIT 1。
＃＆LT; BaseAggregatesRedshiftTest：0x007fd8c4a12580＆GT; {
：truncated_month =＆GT;星期四，2013年1月31日19:00:00 EST -05：00，
：DMA =＆GT; 的CityVille，
：GROUP_ID =＆GT; 9712338，
：dma_id =＆GT; 9999
}
祝你好运@johncorser！
So I have a Redshift database with enough tables that it feels worth my time to build a front end to make querying it a little bit easier than just typing in SQL commands.
Ideally, I'd be able to do this by connecting the database to a Rails app (because I have a bit of experience with Rails). I'm not sure how I'd connect a remote Redshift database to a local Rails application though, or how to make activerecord work with redshift.
Does anyone have any suggestions/resources to help me get started? I'm open to other options to connect the Redshift database to a front end if there are pre-made options easier than Rails.
#app/models/data_warehouse.rb
class DataWarehouse < ActiveRecord::Base
establish_connection "redshift_staging"
#or, if you want to have a db per environment
#establish_connection "redshift_#{Rails.env}"
end
Note that we are connecting on 5439, not the default 5432 so I specify the port Also, I specify a schema, beta, which is what we use for our unstable aggregates, you could either have a different db per environment as mentioned above, or use various schemas and include them in the search path for ActiveRecord
#config/database.yml
redshift_staging:
adapter: postgresql
encoding: utf8
database: db03
port: 5439
pool: 5
schema_search_path: 'beta'
username: admin
password: supersecretpassword
host: db03.myremotehost.us #your remote host here, might be an aws url from Redshift admin console
###OPTION 2, a direct PG Connection
class DataWarehouse < ActiveRecord::Base
attr_accessor :conn
def initialize
@conn = PG.connect(
database: 'db03',
port: 5439,
pool: 5,
schema_search_path: 'beta',
username: 'admin',
password: 'supersecretpassword',
host: 'db03.myremotehost.us'
)
end
end
[DEV] main:0> redshift = DataWarehouse
E, [2014-07-17T11:09:17.758957 #44535] ERROR -- : PG::InsufficientPrivilege: ERROR: permission denied to set parameter "client_min_messages" to "notice" : SET client_min_messages TO 'notice'
(pry) output error: #<ActiveRecord::StatementInvalid: PG::InsufficientPrivilege: ERROR: permission denied to set parameter "client_min_messages" to "notice" : SET client_min_messages TO 'notice'>
UPDATE:
I ended up going with option 1, but using this adapter for now for multiple reasons:
https://github.com/fiksu/activerecord-redshift-adapter
Reason 1: ActiveRecord postgresql adapter sets client_min_messages Reason 2: adapter also attempts to set Time Zone, which redshift doesn't allow (http://docs.aws.amazon.com/redshift/latest/dg/c_redshift-and-postgres-sql.html) Reason 3: Even if you change the code in ActiveRecord for the first two errors, you run into additional errors that complain that Redshift is using Postgresql 8.0, at that point I moved on to the adapter, will revisit and update if I find something better later.
I renamed my table to base_aggregate_redshift_tests (notice plural) so ActiveRecord was easily able to connect, if you can't change your table names in redshift use the set_table method I have commented out below
#Gemfile:
gem 'activerecord4-redshift-adapter', github: 'aamine/activerecord4-redshift-adapter'
Option 1
#config/database.yml
redshift_staging:
adapter: redshift
encoding: utf8
database: db03
port: 5439
pool: 5
username: admin
password: supersecretpassword
host: db03.myremotehost.us
timeout: 5000
#app/models/base_aggregates_redshift_test.rb
#Model named to match my tables in Redshift, if you want you can set_table like I have commented out below
class BaseAggregatesRedshiftTest < ActiveRecord::Base
establish_connection "redshift_staging"
self.table_name = "beta.base_aggregates_v2"
end
in console using self.table_name -- notice it queries the right table, so you can name your models whatever you want
[DEV] main:0> redshift = BaseAggregatesRedshiftTest.first
D, [2014-07-17T15:31:58.678103 #43776] DEBUG -- : BaseAggregatesRedshiftTest Load (45.6ms) SELECT "beta"."base_aggregates_v2".* FROM "beta"."base_aggregates_v2" LIMIT 1
Option 2
#app/models/base_aggregates_redshift_test.rb
class BaseAggregatesRedshiftTest < ActiveRecord::Base
set_table "beta.base_aggregates_v2"
ActiveRecord::Base.establish_connection(
adapter: 'redshift',
encoding: 'utf8',
database: 'staging',
port: '5439',
pool: '5',
username: 'admin',
password: 'supersecretpassword',
search_schema: 'beta',
host: 'db03.myremotehost.us',
timeout: '5000'
)
end
#in console, abbreviated example of first record, now it's using the new name for my redshift table, just assuming I've got the record at base_aggregates_redshift_tests because I didn't set the table_name
[DEV] main:0> redshift = BaseAggregatesRedshiftTest.first
D, [2014-07-17T15:09:39.388918 #11537] DEBUG -- : BaseAggregatesRedshiftTest Load (45.3ms) SELECT "base_aggregates_redshift_tests".* FROM "base_aggregates_redshift_tests" LIMIT 1
#<BaseAggregatesRedshiftTest:0x007fd8c4a12580> {
:truncated_month => Thu, 31 Jan 2013 19:00:00 EST -05:00,
:dma => "Cityville",
:group_id => 9712338,
:dma_id => 9999
}
Good luck @johncorser!
这篇关于我怎样才能建立一个前端查询红移数据库（希望使用Rails）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
