MySQL和Splunk-选择并加入 [英] MySQL and Splunk - Select and Join
本文介绍了MySQL和Splunk-选择并加入的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
在splunk中设置DBconnect查询时，以下代码存在问题.
SELECT * FROM master_biz.legend_asset
RIGHT JOIN
master_custom.custom_app_table_4
ON
master_custom.custom_app_table_4.ID = master_biz.legend_asset.ID
当我使用上面的代码时，它可以在PHPmyAdmin中完美执行.但是，当我尝试在Splunk中使用它时，出现一个错误，指出:
Invalid Query
External search command 'dbxquery' returned error code 1. Script output =
"RuntimeError: Failed to run query: "SELECT * FROM (SELECT * FROM
master_biz.legend_asset RIGHT JOIN master_custom.custom_app_table_4 ON
master_custom.custom_app_table_4.ID = master_biz.legend_asset.ID) t", caused
by:AvroRemoteException(u"com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException : Duplicate column name 'id'",).
此错误表明我有重复的列，称为"ID".我认为这是清理某些数据的绝佳时机，因此我尝试重命名ID字段，如下所示:
SELECT
master_biz.legend_asset.roa_id AS ORGANIZATION_NUMBER,
master_biz.legend_asset.make AS MANUFACTURER,
master_biz.legend_asset.model AS PRODUCT,
master_biz.legend_asset.status AS STATUS,
master_biz.legend_asset.ID AS ASSET_ID
FROM master_biz.legend_asset
RIGHT JOIN master_custom.custom_app_table_4 ON
master_custom.custom_app_table_4.ID = master_biz.legend_asset.ID
但是，当我尝试此查询时，只剩下我重命名的字段，而custom_app_table_4中没有任何字段.认为这可能是由于ID字段的重命名，我将查询更改为:
SELECT
master_biz.legend_asset.roa_id AS ORGANIZATION_NUMBER,
master_biz.legend_asset.make AS MANUFACTURER,
master_biz.legend_asset.model AS PRODUCT,
master_biz.legend_asset.status AS STATUS,
master_biz.legend_asset.ID AS ASSET_ID
FROM master_biz.legend_asset
RIGHT JOIN master_custom.custom_app_table_4 ON
master_custom.custom_app_table_4.ID = master_biz.legend_asset.ASSET_ID
这导致了以下错误:
#1054 - Unknown column master_biz.legend_asset.ASSET_ID in on clause
master_biz.legend_asset表
<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style>
<table class="tableizer-table">
<thead>
<tr class="tableizer-firstrow">
<th>id</th>
<th>did</th>
<th>roa_id</th>
<th>make</th>
<th>model</th>
<th>type</th>
<th>function</th>
<th>status</th>
<th>owner</th>
<th>serial</th>
<th>asset_tag</th>
<th>rfid</th>
<th>date_edit</th>
<th>user_edit</th>
<th>a_notes</th>
<th>owner_admin</th>
<th>owner_tech</th>
</tr>
</thead>
<tbody>
<tr>
<td>2</td>
<td>0</td>
<td>1</td>
<td>Tenable</td>
<td>Nessus</td>
<td> </td>
<td>Unknown</td>
<td>Production</td>
<td> </td>
<td> </td>
<td>NULL</td>
<td> </td>
<td>5/23/2016 16:19</td>
<td>1</td>
<td> </td>
<td>0</td>
<td>0</td>
</tr>
<tr>
<td>3</td>
<td>0</td>
<td>1</td>
<td>Tenable</td>
<td>Nessus</td>
<td> </td>
<td>Unknown</td>
<td>Production</td>
<td> </td>
<td> </td>
<td>NULL</td>
<td> </td>
<td>5/20/2016 18:59</td>
<td>1</td>
<td> </td>
<td>0</td>
<td>0</td>
</tr>
<tr>
<td>4</td>
<td>0</td>
<td>2</td>
<td>Microsoft</td>
<td>Windows Server Standard 2012 R2</td>
<td> </td>
<td>Unknown</td>
<td>Production</td>
<td> </td>
<td> </td>
<td>NULL</td>
<td> </td>
<td>5/20/2016 18:59</td>
<td>1</td>
<td> </td>
<td>0</td>
<td>0</td>
</tr>
<tr>
<td>5</td>
<td>0</td>
<td>0</td>
<td>Solarwinds</td>
<td>Kiwi CAT Tools</td>
<td> </td>
<td>Unknown</td>
<td>Production</td>
<td> </td>
<td> </td>
<td>NULL</td>
<td> </td>
<td>5/20/2016 18:59</td>
<td>1</td>
<td> </td>
<td>0</td>
<td>0</td>
</tr>
<tr>
<td>6</td>
<td>0</td>
<td>1</td>
<td>Splunk</td>
<td>Enterprise</td>
<td> </td>
<td>Unknown</td>
<td>Production</td>
<td> </td>
<td> </td>
<td>NULL</td>
<td> </td>
<td>5/20/2016 18:59</td>
<td>1</td>
<td> </td>
<td>0</td>
<td>0</td>
</tr>
<tr>
<td>7</td>
<td>0</td>
<td>1</td>
<td>Splunk</td>
<td>Enterprise Support</td>
<td> </td>
<td>Unknown</td>
<td>Production</td>
<td> </td>
<td> </td>
<td>NULL</td>
<td> </td>
<td>5/23/2016 16:19</td>
<td>1</td>
<td> </td>
<td>0</td>
<td>0</td>
</tr>
<tr>
<td>8</td>
<td>0</td>
<td>1</td>
<td>VMware</td>
<td>vSphere 5/6 Support Standard</td>
<td> </td>
<td>Unknown</td>
<td>Production</td>
<td> </td>
<td> </td>
<td>NULL</td>
<td> </td>
<td>5/20/2016 18:59</td>
<td>1</td>
<td> </td>
<td>0</td>
<td>0</td>
</tr>
<tr>
<td>9</td>
<td>0</td>
<td>1</td>
<td>VMware</td>
<td>vSphere 5/6 Support Enterprise Plus</td>
<td> </td>
<td>Unknown</td>
<td>Production</td>
<td> </td>
<td> </td>
<td>NULL</td>
<td> </td>
<td>5/20/2016 18:59</td>
<td>1</td>
<td> </td>
<td>0</td>
<td>0</td>
</tr>
<tr>
<td>10</td>
<td>0</td>
<td>1</td>
<td>VMware</td>
<td>vCenter 5/6 Support Standard</td>
<td> </td>
<td>Unknown</td>
<td>Production</td>
<td> </td>
<td> </td>
<td>NULL</td>
<td> </td>
<td>5/20/2016 18:59</td>
<td>1</td>
<td> </td>
<td>0</td>
<td>0</td>
</tr>
</tbody>
</table>
master_biz.asset_location表
<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style>
<table class="tableizer-table">
<thead>
<tr class="tableizer-firstrow">
<th>iid</th>
<th>location</th>
<th>floor</th>
<th>room</th>
<th>plate</th>
<th>panel</th>
<th>punch</th>
<th>zone</th>
<th>rack</th>
<th>shelf</th>
<th>date_edit</th>
<th>user_edit</th>
<th>l_notes</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td> </td>
<td>0</td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td>0000-00-00 00:00:00</td>
<td>1</td>
<td> </td>
</tr>
<tr>
<td>2</td>
<td>Lab</td>
<td>0</td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td>0000-00-00 00:00:00</td>
<td>1</td>
<td> </td>
</tr>
<tr>
<td>3</td>
<td>Production</td>
<td>0</td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td>0000-00-00 00:00:00</td>
<td>1</td>
<td> </td>
</tr>
<tr>
<td>4</td>
<td> </td>
<td>0</td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td>0000-00-00 00:00:00</td>
<td>1</td>
<td> </td>
</tr>
<tr>
<td>5</td>
<td> </td>
<td>0</td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td>0000-00-00 00:00:00</td>
<td>1</td>
<td> </td>
</tr>
<tr>
<td>6</td>
<td> </td>
<td>0</td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td>0000-00-00 00:00:00</td>
<td>1</td>
<td> </td>
</tr>
<tr>
<td>7</td>
<td> </td>
<td>0</td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td>0000-00-00 00:00:00</td>
<td>1</td>
<td> </td>
</tr>
<tr>
<td>8</td>
<td> </td>
<td>0</td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td>0000-00-00 00:00:00</td>
<td>1</td>
<td> </td>
</tr>
<tr>
<td>9</td>
<td> </td>
<td>0</td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td>0000-00-00 00:00:00</td>
<td>1</td>
<td> </td>
</tr>
<tr>
<td>10</td>
<td> </td>
<td>0</td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td>0000-00-00 00:00:00</td>
<td>1</td>
<td></td>
</tr>
</tbody>
</table>
master_custom.custom_app_table_4表
<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style>
<table class="tableizer-table">
<thead>
<tr class="tableizer-firstrow">
<th>id</th>
<th>2_2</th>
<th>8_2</th>
<th>9_2</th>
<th>10_2</th>
<th>11_2</th>
<th>12_2</th>
<th>13_2</th>
</tr>
</thead>
<tbody>
<tr>
<td>2</td>
<td>Software License</td>
<td>Tenable</td>
<td>Professional</td>
<td>1</td>
<td>5/10/2017</td>
<td>2190</td>
<td> </td>
</tr>
<tr>
<td>3</td>
<td>Software License</td>
<td>Tenable</td>
<td>Professional</td>
<td>1</td>
<td>5/10/2017</td>
<td>2190</td>
<td> </td>
</tr>
<tr>
<td>4</td>
<td>Software License</td>
<td>Microsoft</td>
<td>Standard</td>
<td>10</td>
<td>5/3/2016</td>
<td> </td>
<td> </td>
</tr>
<tr>
<td>5</td>
<td>Software Maintenance</td>
<td>Solarwinds</td>
<td>N/A</td>
<td>4</td>
<td>10/30/2016</td>
<td> </td>
<td> </td>
</tr>
<tr>
<td>6</td>
<td>Software License</td>
<td>Splunk</td>
<td>20GB</td>
<td>1</td>
<td>6/1/2016</td>
<td>60000</td>
<td> </td>
</tr>
<tr>
<td>7</td>
<td>Software Maintenance</td>
<td>Splunk</td>
<td>Enterprise</td>
<td>1</td>
<td>6/1/2016</td>
<td>0</td>
<td> </td>
</tr>
<tr>
<td>8</td>
<td>Software Maintenance</td>
<td>VMware</td>
<td>24x7 Production</td>
<td>30</td>
<td>5/10/2017</td>
<td> </td>
<td> </td>
</tr>
<tr>
<td>9</td>
<td>Software Maintenance</td>
<td>VMware</td>
<td>Subscription Only</td>
<td>46</td>
<td>5/10/2017</td>
<td>4375</td>
<td> </td>
</tr>
<tr>
<td>10</td>
<td>Software Maintenance</td>
<td>VMware</td>
<td>Subscription Only</td>
<td>3</td>
<td>5/10/2017</td>
<td>530</td>
<td></td>
</tr>
</tbody>
</table>
最终，我有一个单一的问题试图克服几个障碍.我想将多个数据库中不同表中的选择列连接起来，并将这些列重命名为更合乎逻辑的名称.
Ultimately I have several obstacles I am trying to overcome with a single question. I would like to join select columns from different tables within multiple databases and rename the columns to something that is more logical.
因此，简而言之，我希望能够使用SQL查询将这三个表合并为一个表.如果可能的话，最好更改某些字段的名称，以使数据更易于解释.只能从每个表中选择我想要的字段，真是太棒了.
So in short, I would like to be able to merge these three tables into one table using an SQL query. If possible, it would be nice to change the names of some of the fields so that the data is easier to interpret. It would be awesome to be able to only select the fields I want from each table.
通过组合这些表并仅选择感兴趣的字段，它将节省splunk内的日常使用许可证，磁盘存储空间以及搜索过程中的cpu利用率.
By combining these tables and selecting only the fields that are of interest, it will save on the daily usage license inside splunk, disk storage space, as well as cpu utilization during search.
任何帮助将不胜感激.
Any help is greatly appreciated.
推荐答案
SELECT *是反模式.如果id仅是两个表中都存在的列，则可以使用:
SELECT * is antipattern. If id is only column that exists in both tables you could use:
SELECT *
FROM master_biz.legend_asset
RIGHT JOIN master_custom.custom_app_table_4
USING (id);
否则，您需要为每列手动添加别名:
Otherwise you need to add alias for each column manually:
SELECT a.ID AS id
,a. ... AS ...
,t4.col AS ...
FROM master_biz.legend_asset a
RIGHT JOIN master_custom.custom_app_table_4 t4
ON a.ID = t4.ID;
注意:您无需输入表名，可以使用表别名.
Note: You don't need to write table name, you could use table aliases.
代码的JOIN ON和JOIN USING部分有什么区别?
what are the differences in the JOIN ON and JOIN USING parts of the code?
USING将返回一次在JOIN中使用的列:
USING will return columns that are used in JOIN once:
SELECT *
FROM t1
JOIN t2
USING(i);
SELECT *
FROM t1
JOIN t2
ON t1.i = t2.i;
输出:
╔════╦════╦═══╗
║ i ║ b ║ c ║
╠════╬════╬═══╣
║ 1 ║ 1 ║ 3 ║
╚════╩════╩═══╝
vs.
╔════╦════╦════╦═══╗
║ i ║ b ║ i ║ c ║
╠════╬════╬════╬═══╣
║ 1 ║ 1 ║ 1 ║ 3 ║
╚════╩════╩════╩═══╝
这篇关于MySQL和Splunk-选择并加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
